package com.eorchis.module.classuser.dao.impl;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.validation.Valid;

import org.hibernate.Session;
import org.springframework.stereotype.Repository;
import org.springframework.web.bind.annotation.ModelAttribute;

import com.eorchis.components.attachment.ui.commond.AttachmentCommond;
import com.eorchis.constants.DeptConstants;
import com.eorchis.constants.SystemConstants;
import com.eorchis.core.basedao.dao.impl.HibernateAbstractBaseDao;
import com.eorchis.core.basedao.entity.IBaseEntity;
import com.eorchis.core.basedao.query.condition.IPagingQueryCondition;
import com.eorchis.core.basedao.query.condition.builder.CompareType;
import com.eorchis.core.basedao.query.condition.builder.IConditionBuilder;
import com.eorchis.core.basedao.query.condition.builder.OrderType;
import com.eorchis.core.basedao.query.condition.builder.impl.DefaultQueryConditionBuilder;
import com.eorchis.core.basedao.query.condition.impl.BaseQueryCondition;
import com.eorchis.core.basedao.query.mapper.MapperType;
import com.eorchis.core.basedao.query.mapper.ResultMapperFactory;
import com.eorchis.core.basedao.query.mapper.impl.BeanResultMapper;
import com.eorchis.core.page.commond.SortInfoBean;
import com.eorchis.core.ui.commond.IQueryCommond;
import com.eorchis.module.TrainingConstants;
import com.eorchis.module.addUser.bean.UserBean;
import com.eorchis.module.basedata.domain.BaseData;
import com.eorchis.module.classuser.dao.IClassUserDao;
import com.eorchis.module.classuser.domain.ClassUser;
import com.eorchis.module.classuser.domain.ClassUserCommond;
import com.eorchis.module.classuser.domain.ClassUserQueryBean;
import com.eorchis.module.classuser.domain.QueryClassUserBean;
import com.eorchis.module.classuser.domain.QueryTrainingHoursBean;
import com.eorchis.module.classuser.domain.RecordInfoBean;
import com.eorchis.module.classuser.domain.UserClassTypeHour;
import com.eorchis.module.classuser.ui.commond.ClassUserQueryCommond;
import com.eorchis.module.classuser.ui.commond.ClassUserValidCommond;
import com.eorchis.module.department.domain.Department;
import com.eorchis.module.trainingclass.domain.TrainingClass;
import com.eorchis.module.trainingclass.domain.TrainingClassQueryBean;
import com.eorchis.module.trainingclass.ui.commond.TrainingClassQueryCommond;
import com.eorchis.module.user.domain.User;
import com.eorchis.module.user.ui.commond.UserInfoValidCommond;
import com.eorchis.module.util.SqlQueryUtil;
import com.eorchis.utils.utils.PropertyUtil;

/**
 * Title: ClassUserDaoImpl<br>
 * Description: 培训班用户关系数据接口实现<br>
 * Company: GoldGov<br>
 * Copyright @ 2012 GoldGov .All rights reserved.<br>
 * 
 * @createDate 2013-12-10
 * @version $Revision$
 */
@SuppressWarnings({ "unchecked", "deprecation" })
@Repository(value = "com.eorchis.module.classuser.dao.impl.ClassUserDaoImpl")
public class ClassUserDaoImpl extends HibernateAbstractBaseDao implements IClassUserDao {

	@Override
	public Class<? extends IBaseEntity> entityClass() {
		return ClassUser.class;
	}

	@Override
	public void queryConditionProcessor(IConditionBuilder defaultQueryConditionBuilder, IQueryCommond queryCommond) {
		ClassUserQueryCommond classUserQueryCommond = (ClassUserQueryCommond) queryCommond;
		// 添加学时字段trainingHours By HuangTF At 2013-12-12
		// 添加上报学时状态字段reportedHoursState By HuangTF At 2013-12-23
		// 添加审核学时状态字段auditingHoursState By HuangTF At 2013-12-23
		// 添加干教同步学时相关字段gjSyncTrainingHours,gjSyncHoursState By HuangTF At
		// 2014-2-24
		defaultQueryConditionBuilder.setBaseQueryString("SELECT t.classUserId AS classUserId,t.trainingClass.classId AS classId,t.userName AS userName,dll.dataName as rank,t.sexCode.dataName AS sex,t.folkCode.dataName AS folkName,t.birthday AS birthday,d.deptName AS depName, "
				+ "t.dutyRemark AS position,t.remark AS remark,t.user.loginID AS loginID,t.auditingState AS auditingState,t.user.userId AS userId,t.mobileTelephone as phone,d.deptID AS deptId,t.auditingUserDate as auditingStateDate,t.appraise as appraise,t.appraiseStatus as appraiseStatus "
				+ " FROM ClassUser t left join t.user.dutyLevelCode as dll left join t.folkCode left join t.user.administrativeLevel as al left join t.sexCode,Department d,DepartmentUser du  where  t.user.userId = du.user.userId and du.department.deptID = d.deptID and");
		// defaultQueryConditionBuilder.addCondition("t.classUserId",
		// CompareType.IN, classUserQueryCommond.getSearchClassUserIds());
		// defaultQueryConditionBuilder.addCondition("t.trainingClass.classId",
		// CompareType.EQUAL,classUserQueryCommond.getSearchClassID());
		// defaultQueryConditionBuilder.addCondition("t.user.userName",
		// CompareType.LIKE,classUserQueryCommond.getSearchUserName());
		// defaultQueryConditionBuilder.addCondition("t.user.loginID",
		// CompareType.LIKE,classUserQueryCommond.getSearchLoginID());
		if (PropertyUtil.objectNotEmpty(classUserQueryCommond.getSearchClassID())) {
			defaultQueryConditionBuilder.addCondition("t.trainingClass.classId", CompareType.EQUAL, classUserQueryCommond.getSearchClassID());
		}
		if (PropertyUtil.objectNotEmpty(classUserQueryCommond.getSearchMinistrieID())) {
			defaultQueryConditionBuilder.addCondition("t.trainingClass.ministrieID", CompareType.EQUAL, classUserQueryCommond.getSearchMinistrieID());
		}
		if (PropertyUtil.objectNotEmpty(classUserQueryCommond.getSearchUserName())) {
			String userName = SqlQueryUtil.changeString(classUserQueryCommond.getSearchUserName().replace(" ", ""));
			defaultQueryConditionBuilder.addCondition("t.userName", CompareType.LIKE, userName);
		}
		if (PropertyUtil.objectNotEmpty(classUserQueryCommond.getSearchLoginID())) {
			defaultQueryConditionBuilder.addCondition("t.user.loginID", CompareType.EQUAL, classUserQueryCommond.getSearchLoginID());
		}
		if (PropertyUtil.objectNotEmpty(classUserQueryCommond.getSearchDepName())) {
			defaultQueryConditionBuilder.addCondition("d.deptName", CompareType.LIKE, classUserQueryCommond.getSearchDepName().replace(" ", ""));
		}
		if (PropertyUtil.objectNotEmpty(classUserQueryCommond.getAuditingState())) {
			if (classUserQueryCommond.getAuditingState() == ClassUser.IS_AUDITING_Y.intValue())
				defaultQueryConditionBuilder.addCondition("t.auditingState", CompareType.IN, new Integer[] { ClassUser.IS_AUDITING_Y, ClassUser.IS_AUDITING_Y_CX_N });
			else if (classUserQueryCommond.getAuditingState() == ClassUser.IS_AUDITING_N.intValue())
				defaultQueryConditionBuilder.addCondition("t.auditingState", CompareType.NOT_EQUAL, ClassUser.IS_AUDITING_N);
			else
				defaultQueryConditionBuilder.addCondition("t.auditingState", CompareType.EQUAL, classUserQueryCommond.getAuditingState());
		}
		if (PropertyUtil.objectNotEmpty(classUserQueryCommond.getAppraiseStatus())) {
			defaultQueryConditionBuilder.addCondition("t.appraiseStatus", CompareType.EQUAL, classUserQueryCommond.getAppraiseStatus());
		}
		// 单位上报区级项目中添加查询条件：单位路径 By NingSS At 2013-12-25
		if (PropertyUtil.objectNotEmpty(classUserQueryCommond.getSearchDepId())) {
			try {
				List<Department> department = this.findDepTreePath(classUserQueryCommond);
				String treepath = department.get(0).getTreepath();
				classUserQueryCommond.setSearchTreePath(treepath);
				defaultQueryConditionBuilder.addCondition("d.treepath", CompareType.LIKE, classUserQueryCommond.getSearchTreePath());
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		// defaultQueryConditionBuilder.addSort("
		// nlssort(al.dataName,'NLS_SORT=SCHINESE_PINYIN_M')",
		// OrderType.ASC);
		// defaultQueryConditionBuilder.addSort("d.orderNum2", OrderType.ASC);
		defaultQueryConditionBuilder.addSort("t.dutyLevelCode.orderNum", OrderType.ASC);
		defaultQueryConditionBuilder.addSort("t.user.loginID", OrderType.ASC);
		// defaultQueryConditionBuilder.addSort("t.createDate", OrderType.DESC);
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.HQL);
		defaultQueryConditionBuilder.setResultObject(QueryClassUserBean.class);
	}

	/**
	 * 更新用户培训学时
	 * 
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2013-12-11
	 */
	public void updateTrainingHours(ClassUserValidCommond commond) throws Exception {
		String hql = "update ClassUser c set c.trainingHours=:trainingHours,c.reportedHoursState=:reportedHoursState,c.auditingHoursState=:auditingHoursState where c.classUserId = :classUserId";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("trainingHours", commond.getTrainingHours());
		params.put("classUserId", commond.getClassUserId());
		this.executeUpdate(QueryStringType.HQL, hql, params);
	}

	/**
	 * 更新用户学时的时候查询班级学时
	 * 
	 * @param classUserId
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-1-17
	 */
	public Double queryTrainingHours(String classUserId) throws Exception {
		String sql = "SELECT tc.TRAINING_DAY as trainingDay,tc.TRAINING_HOUR as trainingHour " + " FROM training_class tc " + " LEFT JOIN training_class_user tcu ON tc.CLASS_ID=tcu.CLASS_ID " + " WHERE tcu.CLASS_USER_ID = :classUserId";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("classUserId", classUserId);
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(sql);
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		BeanResultMapper mapper = (BeanResultMapper) ResultMapperFactory.getInstance().getMapper(MapperType.Bean);
		mapper.setResultClass(QueryTrainingHoursBean.class);
		queryCondition.setResultMapper(mapper);

		List<QueryTrainingHoursBean> findList = (List<QueryTrainingHoursBean>) this.findList(queryCondition);
		QueryTrainingHoursBean bean = findList.get(0);
		if (PropertyUtil.objectNotEmpty(bean.getTrainingDay())) {
			return bean.getTrainingDay() * 8;
		} else {
			return bean.getTrainingHour();
		}
	}

	/**
	 * 由classUserId查找班级id
	 * 
	 * @param classUserId
	 * @return
	 * @throws Exception
	 * @author NingSS
	 * @createDate 2014-1-21
	 */
	public QueryTrainingHoursBean queryClassId(String classUserId) throws Exception {
		String sql = "SELECT tc.CLASS_ID as classId" + " FROM training_class tc " + " LEFT JOIN training_class_user tcu ON tc.CLASS_ID=tcu.CLASS_ID " + " WHERE tcu.CLASS_USER_ID = :classUserId";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("classUserId", classUserId);
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(sql);
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		BeanResultMapper mapper = (BeanResultMapper) ResultMapperFactory.getInstance().getMapper(MapperType.Bean);
		mapper.setResultClass(QueryTrainingHoursBean.class);
		queryCondition.setResultMapper(mapper);

		List<QueryTrainingHoursBean> findList = (List<QueryTrainingHoursBean>) this.findList(queryCondition);
		QueryTrainingHoursBean bean = findList.get(0);
		return bean;
	}

	/**
	 * 查询单位treePath
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author NingSS
	 * @createDate 2012-12-10
	 */
	@Override
	public List<Department> findDepTreePath(ClassUserQueryCommond queryCommond) throws Exception {
		StringBuffer hql = new StringBuffer("from Department ff where 1=1");
		Map<String, Object> values = new HashMap<String, Object>();
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepId())) {
			hql.append(" and ff.deptID =:depid");
			values.put("depid", queryCommond.getSearchDepId());
		}
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(hql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.HQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		return (List<Department>) this.findList(queryCondition);
	}

	/**
	 * 查询未选人员列表
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author NingSS
	 * @createDate 2012-12-10
	 */
	@Override
	public List<QueryClassUserBean> findNoSelectedUserList(ClassUserQueryCommond queryCommond) throws Exception {
		StringBuffer querySql = new StringBuffer("select t.user_name as \"userName\" ,bb.data_name as \"sex\",bb.DATA_ID as \"sexCode\",t.birthday as \"birthday\",");
		if (queryCommond.getIsSuperBusinessAdmin())
			querySql.append("(case when ud.depid=" + DeptConstants.DEPT_ROOT_ID + " then ud.depname else ld.depname end)");
		else
			querySql.append("ud.depname");
		querySql.append(" as \"depName\",date_format(t.birthday,'%y-%m') as \"birthdayStr\",t.duty_remark as \"position\",t.paper_num as \"paperNum\"," 
			+ "t.login_id as \"loginID\",bbb.data_name \"rank\",bbb.DATA_ID as \"rankCode\",concat(t.userid,'') as \"userId\",ud.depid as \"deptId\","
				+ "t.mobile_Telephone as \"mobileTelephone\",case bt.DATA_ID when '-100' then '' else bt.DATA_ID end as \"folkCode\""
				+ ",case bt.data_name when '无' then '' else bt.data_name end as \"folkName\" ");
		StringBuffer sql = new StringBuffer(" from " + SystemConstants.SYSTEM_CODE + "uum_user t LEFT JOIN " 
				+ SystemConstants.SYSTEM_CODE + "uum_BASEDATA bb ON t.sex_code = bb.DATA_ID" + " left join " 
				+ SystemConstants.SYSTEM_CODE + "uum_basedata bbb on t.duty_Level_Code = bbb.DATA_ID"
				+ " left join " + SystemConstants.SYSTEM_CODE + "uum_basedata bt on t.folk_code = bt.data_id" 
				+ " left join " + SystemConstants.SYSTEM_CODE + "uum_department_user udu on t.userid = udu.userid" 
				+ " left join  " + SystemConstants.SYSTEM_CODE + "uum_department ud on udu.depid = ud.depid");
		if (queryCommond.getIsSuperBusinessAdmin())
			sql.append(" left join base_department ld on udu.level_depid = ld.depid");
		sql.append(" where t.active_state = :activeState and t.login_id not like '9%' and t.user_name not like '%管理员' " + " and t.user_name <> '通讯员' ");
		Map<String, Object> values = new HashMap<String, Object>();
		// 当查询条件为指定人员时则去除部门限制
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserIds())) {
			sql.append(" and t.userid in (:userIds)");
			values.put("userIds", queryCommond.getSearchUserIds());
		} else if (PropertyUtil.objectNotEmpty(queryCommond.getSearchTreePath())) {
			// sql.append(" and ud.treepath =:treepath");
			sql.append(" and instr(ud.treepath , '" + queryCommond.getSearchTreePath() + "') = 1");
			// values.put("treepath", queryCommond.getSearchTreePath());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchTreePaths())) {
			sql.append(" and " + queryCommond.getSearchTreePaths()[0]);
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchNotDepId())) {
			sql.append(" and ud.depid!=:depid");
			values.put("depid", queryCommond.getSearchNotDepId());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassID())) {
			// sql.append(" and t.userid not in(select tcu.user_id from
			// TRAINING_CLASS_USER tcu where tcu.class_id=:classId)");
			values.put("classId", queryCommond.getSearchClassID());
			List<String> list = (List<String>) this.executeFind(QueryStringType.SQL, "SELECT tcu.user_id FROM TRAINING_CLASS_USER tcu WHERE tcu.class_id =:classId", values);
			if (!list.isEmpty()) {
				String ite[] = new String[list.size()];
				for (int i = 0; i < list.size(); i++) {
					ite[i] = list.get(i);
				}
				sql.append(" and t.USERID not in (:userIdss)");
				values.put("userIdss", ite);
			}
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())) {
			queryCommond.setSearchUserName(queryCommond.getSearchUserName().replace(",", "，"));
			if (queryCommond.getSearchUserName().split("，").length > 1) {
				sql.append(" and (");
				for (String s : queryCommond.getSearchUserName().split("，")) {
					sql.append(" t.user_name like '" + SqlQueryUtil.changeString(s) + "' or");
				}
				sql = new StringBuffer(sql.substring(0, sql.length() - 2));
				sql.append(")");
			} else {
				sql.append(" and t.user_name like :userName");
				String userName = SqlQueryUtil.changeString(queryCommond.getSearchUserName().replace(" ", ""));
				values.put("userName", userName);
			}
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDutyRemark())) {
			sql.append(" and  t.duty_remark like :dutyRemark");
			values.put("dutyRemark", "%" + queryCommond.getSearchDutyRemark() + "%");
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchLoginID())) {
			sql.append(" and t.login_id=:loginId");
			values.put("loginId", queryCommond.getSearchLoginID());
		}

		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchRank())) {
			sql.append(" and t.duty_Level_Code=:searchRank");
			values.put("searchRank", queryCommond.getSearchRank());
		}

		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepName())) {
			if (queryCommond.getIsSuperBusinessAdmin())
				sql.append(" and ld.depname like :depName");
			else
				sql.append(" and ud.depname like :depName");
			values.put("depName", "%" + queryCommond.getSearchDepName().replace(" ", "") + "%");
		}
		values.put("activeState", ClassUser.IS_ACTIVE_STATE_Y);
		// 增加排序
		if (queryCommond.getSortInfos() != null) {
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
				// 返回部门名称，判断是否是超级管理员
				// 如果是超级管理员用d.depname，如果是else的一级管理员用ud.depname，因为上面就是这么写的
				String deptNameing = sortInfoBean.getProperty().trim();
				if (deptNameing != null && deptNameing.equals("d.depname")) {
					if (queryCommond.getIsSuperBusinessAdmin()) {
						deptNameing = "ld.depname";
					} else {
						deptNameing = "ud.depname";
					}
					sql.append(" order by " + deptNameing);
				} else {
					String sex_code = sortInfoBean.getProperty().trim();
					if (sex_code != null && sex_code.equals("c.sex_code")) {
						sex_code = "t.sex_code";
						sql.append(" order by " + sex_code);
					} else {
						String user_Name = sortInfoBean.getProperty().trim();
						if (user_Name != null && user_Name.equals("c.user_Name")) {
							user_Name = "t.user_Name";
							sql.append(" order by " + user_Name);
						} else {
							String login_ID = sortInfoBean.getProperty().trim();
							if (login_ID != null && login_ID.equals("bu.login_ID")) {
								login_ID = "t.login_ID";
								sql.append(" order by " + login_ID);
							} else {
								String duty_Remark = sortInfoBean.getProperty().trim();
								if (duty_Remark != null && duty_Remark.equals("c.duty_Remark")) {
									duty_Remark = "t.duty_Remark";
									sql.append(" order by " + duty_Remark);
								} else {
									String folk_Code = sortInfoBean.getProperty().trim();
									if (folk_Code != null && folk_Code.equals("c.folk_Code")) {
										folk_Code = "t.folk_Code";
										sql.append(" order by " + folk_Code);
									} else {
										String rank = sortInfoBean.getProperty().trim();
										if (rank != null && rank.equals("rank")) {
											rank = "bbb.data_id";
											sql.append(" order by " + rank);
										} else {
											String birthday = sortInfoBean.getProperty().trim();
											if (birthday != null && birthday.equals("birthdayStr")) {
												birthday = "birthday";
												sql.append(" order by " + birthday);
											} else {
												sql.append(" order by " + sortInfoBean.getProperty().trim());
											}
										}
									}
								}
							}
						}
					}
				}
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
					sql.append(" " + sortInfoBean.getDirection().trim());
				}
			}
		} else
			sql.append(" order by bbb.data_seq, t.login_id");
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(querySql.append(sql.toString()).toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		// Long count = this.count(queryCondition);

		// 查询总数量
		List<BigInteger> executeFind = (List<BigInteger>) this.executeFind(QueryStringType.SQL, "select count(1) " + sql.toString(), values);
		Long count = Long.valueOf(executeFind.get(0).toString());

		List<QueryClassUserBean> findList = new ArrayList<QueryClassUserBean>();

		BeanResultMapper mapper = (BeanResultMapper) ResultMapperFactory.getInstance().getMapper(MapperType.Bean);
		mapper.setResultClass(QueryClassUserBean.class);
		queryCondition.setResultMapper(mapper);

		if (queryCommond.getPage() == -1) {
			findList = (List<QueryClassUserBean>) this.findList(queryCondition);
		} else {
			int firstResult = queryCommond.calculate(count.longValue());
			findList = (List<QueryClassUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
		}
		return findList;
	}

	/**
	 * 结班时统一更新用户培训学时
	 * 
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2013-12-20
	 */
	public void updateTrainingHoursOnEnd(ClassUserValidCommond commond) throws Exception {
		StringBuffer hql = new StringBuffer("update ClassUser c set c.trainingHours=:trainingHours ");
		// if(PropertyUtil.objectNotEmpty(commond.getAuditingHoursState()))
		// {
		// hql.append(",c.auditingHoursState="+commond.getAuditingHoursState());
		// }
		hql.append("where c.trainingClass.classId = :classId and c.auditingState = :auditingState");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("trainingHours", commond.getTrainingHours());
		params.put("classId", commond.getTrainingClass().getClassId());
		params.put("auditingState", commond.getAuditingState());
		this.executeUpdate(QueryStringType.HQL, hql.toString(), params);
	}

	/**
	 * 结班时统一更新用户培训学时
	 * 
	 * @param commond
	 * @throws Exception
	 * @author RongSL
	 * @createDate 2017-12-22
	 */
	public void updateTrainingHoursOnEnd2(ClassUserValidCommond commond) throws Exception {
		StringBuffer hql = new StringBuffer("update ClassUser c set c.trainingHours=:trainingHours ");
		hql.append("where c.trainingClass.classId = :classId and c.auditingState = :auditingState");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("trainingHours", commond.getTrainingHours());
		params.put("classId", commond.getTrainingClass().getClassId());
		params.put("auditingState", commond.getAuditingState());
		this.executeUpdate(QueryStringType.HQL, hql.toString(), params);
	}

	/**
	 * 填写学时页面添加学员时如果超过班级上限则扩大班级上限
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2013-12-23
	 */
	public void updateClassUserNum(String classId, int num) throws Exception {
		String hql = "update TrainingClass tc set tc.classUserNum = :classUserNum where tc.classId = :classId";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("classUserNum", num);
		params.put("classId", classId);
		this.executeUpdate(QueryStringType.HQL, hql, params);
	}

	/**
	 * 单位上报学时
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2013-12-23
	 */
	public void updateReportedHoursState(ClassUserQueryCommond queryCommond) throws Exception {
		String hql = "update ClassUser c set c.reportedHoursState=:reportedHoursState where c.classUserId in (:classUserIds)";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("classUserIds", queryCommond.getSearchClassUserIds());
		params.put("reportedHoursState", queryCommond.getReportedHoursState());
		this.executeUpdate(QueryStringType.HQL, hql, params);
	}

	/**
	 * 再次报名
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author NingSS
	 * @createDate 2013-12-23
	 */
	public void updateAuditingState(ClassUserQueryCommond queryCommond) throws Exception {
		String hql = "update ClassUser c set c.auditingState=:auditingState, c.latestOperateDate = :latestOperateDate where c.classUserId in(:classUserIds)";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("auditingState", ClassUser.NO_AUDITING);
		params.put("latestOperateDate", new Date());
		params.put("classUserIds", queryCommond.getSearchClassUserIds());
		this.executeUpdate(QueryStringType.HQL, hql, params);
	}

	/**
	 * 处理发放给单位填写学时
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2013-12-25
	 */
	public void updateWriteHoursRank(ClassUserQueryCommond queryCommond) throws Exception {
		String hql = "update ClassUser c set c.writeHoursRank=:writeHoursRank where c.trainingClass.classId = :classId and c.user.userId in(:userIds)";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("writeHoursRank", queryCommond.getWriteHoursRank());
		params.put("classId", queryCommond.getSearchClassID());
		params.put("userIds", queryCommond.getSearchUserIds());

		this.executeUpdate(QueryStringType.HQL, hql, params);
	}

	/**
	 * 发放给单位填写学时页面待添加学员列表
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-1-6
	 */
	public List<QueryClassUserBean> findListUserToDW(ClassUserQueryCommond queryCommond) throws Exception {
		StringBuffer hql = new StringBuffer("");
		hql.append("select t.user.userId as userId,t.user.userName as userName,t.user.sexCode.dataName as sex,t.user.birthday as birthday," + "t.department.deptName as depName,t.user.dutyRemark as position,t.user.dutyLevelCode.dataName as rank,"
				+ "t.user.loginID as loginID from DepartmentUser t where 1=1");
		Map<String, Object> params = new HashMap<String, Object>();
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassID())) {
			hql.append(" and t.user.userId not in (select cu.user.userId from ClassUser cu where cu.trainingClass.classId = :classId)");
			params.put("classId", queryCommond.getSearchClassID());

			String[] rankCodes = this.findClassUserRank(queryCommond.getSearchClassID());
			if (rankCodes.length > 0) {
				hql.append(" and t.user.dutyLevelCode.dataCode in (:rankCodes)");
				params.put("rankCodes", rankCodes);
			}
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchTreePath())) {
			hql.append(" and t.department.treepath like :treePath");
			params.put("treePath", queryCommond.getSearchTreePath() + "%");
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())) {
			String userName = SqlQueryUtil.changeString(queryCommond.getSearchUserName().replace(" ", ""));
			hql.append(" and t.user.userName like :userName");
			params.put("userName", userName);
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchLoginID())) {
			hql.append(" and t.user.loginID like :loginID");
			params.put("loginID", queryCommond.getSearchLoginID());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchRank())) {
			hql.append(" and t.user.dutyLevelCode.dataCode = :dutyLevelCode");
			params.put("dutyLevelCode", queryCommond.getSearchRank());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepName())) {
			hql.append(" and t.department.deptName like :deptName");
			params.put("deptName", "%" + queryCommond.getSearchDepName() + "%");
		}

		// 增加排序
		if (queryCommond.getSortInfos() != null) {
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
				hql.append(" order by " + sortInfoBean.getProperty().trim());
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
					hql.append(" " + sortInfoBean.getDirection().trim());
				}
			}
		}
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(hql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.HQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		Long count = this.count(queryCondition);
		List<QueryClassUserBean> findList = new ArrayList<QueryClassUserBean>();

		BeanResultMapper mapper = (BeanResultMapper) ResultMapperFactory.getInstance().getMapper(MapperType.Bean);
		mapper.setResultClass(QueryClassUserBean.class);
		queryCondition.setResultMapper(mapper);

		if (queryCommond.getPage() == -1) {
			findList = (List<QueryClassUserBean>) this.findList(queryCondition);
		} else {
			int firstResult = queryCommond.calculate(count.longValue());
			findList = (List<QueryClassUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
		}
		return findList;
	}

	/**
	 * 判断人员是否在班级
	 * 
	 * @param classId
	 *            ,userId
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-1-3
	 */
	public boolean isOnClass(String classId, String userId) throws Exception {
		String hql = "from ClassUser c where c.trainingClass.classId =:classId and c.user.userId = :userId";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("classId", classId);
		params.put("userId", userId);
		if (this.executeFind(QueryStringType.HQL, hql, params).size() > 0)
			return true;
		else
			return false;

	}

	/**
	 * 单位填报市级项目查询班级列表
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2013-12-26
	 */
	public List<TrainingClassQueryBean> findTrainingClassList(TrainingClassQueryCommond queryCommond) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ( SELECT DISTINCT tc.CLASS_ID AS classId, tc.CLASS_MAIN_NAME AS className, bb.DATA_NAME AS classType," + " tc.ORGANIZER_NAME AS organizerName,tc.TRAINING_DAY AS trainingDay,tc.TRAINING_HOUR AS trainingHour," + " tc.BEGIN_DATE AS beginDate,tc.END_DATE AS endDate,"
				+ " (SELECT COUNT(tcu_1.CLASS_USER_ID) FROM training_class_user tcu_1 " + " WHERE tcu_1.USER_ID IN (SELECT bdu.USERID FROM base_department_user bdu WHERE bdu.DEPID = :deptID) " + " AND tcu_1.REPORTED_HOURS_STATE=2 AND tcu_1.WRITE_HOURS_RANK=2 AND tcu_1.CLASS_ID=tcu.CLASS_ID"
				+ " ) AS reportedNumBig," + " (SELECT COUNT(tcu_2.CLASS_USER_ID) FROM training_class_user tcu_2 " + " WHERE tcu_2.USER_ID IN (SELECT bdu.USERID FROM base_department_user bdu WHERE bdu.DEPID = :deptID) "
				+ " AND tcu_2.AUDITING_HOURS_STATE=2 AND tcu_2.WRITE_HOURS_RANK=2 AND tcu_2.CLASS_ID=tcu.CLASS_ID" + " ) AS auditNoPassBig,tc.CREATE_DATE as createDate " + " FROM training_class_user tcu" + " LEFT JOIN training_class tc ON tcu.CLASS_ID = tc.CLASS_ID"
				+ " LEFT JOIN base_basedata bb ON tc.CLASS_TYPE=bb.DATA_CODE" + " WHERE tcu.WRITE_HOURS_RANK = 2 ");
		sql.append(" AND tcu.USER_ID IN (SELECT bdu.USERID FROM base_department_user bdu WHERE bdu.DEPID = :deptID)");
		Map<String, Object> params = new HashMap<String, Object>();
		// 部门ID
		params.put("deptID", queryCommond.getSearchDepId());
		// 结班状态
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchEndState())) {
			sql.append(" and tc.END_STATE = :endState");
			params.put("endState", queryCommond.getSearchEndState());
		}
		// 培训班名称
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassName())) {
			sql.append(" and tc.CLASS_MAIN_NAME like :className");
			params.put("className", "%" + queryCommond.getSearchClassName().replace(" ", "") + "%");
		}
		// 培训班类型
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassType())) {
			sql.append(" and tc.CLASS_TYPE = :classType");
			params.put("classType", queryCommond.getSearchClassType());
		}
		// 主办单位
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchOrganizerName())) {
			sql.append(" and tc.ORGANIZER_NAME like :organizerName");
			params.put("organizerName", "%" + queryCommond.getSearchOrganizerName().replace(" ", "") + "%");
		}
		// 开班时间
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchBeginDate())) {
			sql.append(" and tc.BEGIN_DATE like :beginDate");
			params.put("beginDate", "%" + queryCommond.getSearchBeginDate() + "%");
		}
		// 结班时间
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchEndDate())) {
			sql.append(" and tc.END_DATE like :endDate");
			params.put("endDate", "%" + queryCommond.getSearchEndDate() + "%");
		}
		// 只查询市级班
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassCategory())) {
			sql.append(" AND tc.CLASS_CATEGORY = :classCategory");
			params.put("classCategory", queryCommond.getSearchClassCategory());
		}

		sql.append(" ) as t");

		// 增加排序
		if (queryCommond.getSortInfos() != null) {
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
				sql.append(" order by " + sortInfoBean.getProperty().trim());
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
					sql.append(" " + sortInfoBean.getDirection().trim());
				}
			}
		} else {
			sql.append(" order by t.endDate DESC,t.createDate DESC");
		}

		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		Long count = this.count(queryCondition);

		List<TrainingClassQueryBean> findList = new ArrayList<TrainingClassQueryBean>();

		BeanResultMapper mapper = (BeanResultMapper) ResultMapperFactory.getInstance().getMapper(MapperType.Bean);
		mapper.setResultClass(TrainingClassQueryBean.class);
		queryCondition.setResultMapper(mapper);
		this.findList(queryCondition);

		if (queryCommond.getPage() == -1) {
			findList = (List<TrainingClassQueryBean>) this.findList(queryCondition);
		} else {
			int firstResult = queryCommond.calculate(count.longValue());
			findList = (List<TrainingClassQueryBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
		}

		return findList;
	}

	/**
	 * 单位填报市级项目查询人员信息
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2013-12-30
	 */
	@Override
	public List<QueryClassUserBean> findUserFillIn(ClassUserQueryCommond queryCommond) throws Exception {
		StringBuffer hql = new StringBuffer(
				"SELECT t.auditingHoursState as auditingHoursState,t.reportedHoursState as reportedHoursState,t.trainingHours as trainingHours,t.classUserId AS classUserId,t.trainingClass.classId AS classId,t.user.userName AS userName,t.user.sexCode.dataName AS sex,t.user.birthday AS birthday,d.deptName AS depName,"
						+ "t.user.dutyRemark AS position,t.user.dutyLevelCode.dataName AS rank,t.user.loginID AS loginID,t.auditingState AS auditingState,t.user.userId AS userId,d.deptID AS deptId"
						+ " FROM ClassUser t left join t.user.sexCode left join t.user.dutyLevelCode,Department d,DepartmentUser du where  t.user.userId = du.user.userId and du.department.deptID = d.deptID");
		Map<String, Object> params = new HashMap<String, Object>();
		// 培训班ID
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassID())) {
			hql.append(" and t.trainingClass.classId = :classId");
			params.put("classId", queryCommond.getSearchClassID());
		}
		// 单位ID
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepId())) {
			hql.append(" and d.deptID = :deptID");
			params.put("deptID", queryCommond.getSearchDepId());
		}
		// 学时上报状态
		if (PropertyUtil.objectNotEmpty(queryCommond.getReportedHoursState())) {
			hql.append(" and t.reportedHoursState = :reportedHoursState");
			params.put("reportedHoursState", queryCommond.getReportedHoursState());
		}
		// 学时审核状态
		if (PropertyUtil.objectNotEmpty(queryCommond.getAuditingHoursState())) {
			hql.append(" and t.auditingHoursState = :auditingHoursState");
			params.put("auditingHoursState", queryCommond.getAuditingHoursState());
		}
		// 由单位填写学时
		if (PropertyUtil.objectNotEmpty(queryCommond.getWriteHoursRank())) {
			hql.append(" and t.writeHoursRank = :writeHoursRank");
			params.put("writeHoursRank", queryCommond.getWriteHoursRank());
		}
		// 学员姓名
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())) {
			String userName = SqlQueryUtil.changeString(queryCommond.getSearchUserName().replace(" ", ""));
			hql.append(" and t.user.userName like :userName");
			params.put("userName", userName);
		}
		// 登陆id
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchLoginID())) {
			hql.append(" and t.user.loginID = :loginID");
			params.put("loginID", queryCommond.getSearchLoginID());
		}
		// 单位名称
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepName())) {
			hql.append(" and d.deptName like :deptName");
			params.put("deptName", "%" + queryCommond.getSearchDepName().replace(" ", "") + "%");
		}
		// 职级
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchRank())) {
			hql.append(" and t.user.dutyLevelCode.dataCode = :rank");
			params.put("rank", queryCommond.getSearchRank());
		}

		// 增加排序
		if (queryCommond.getSortInfos() != null) {
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
				hql.append(" order by " + sortInfoBean.getProperty().trim());
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
					hql.append(" " + sortInfoBean.getDirection().trim());
				}
			}
		}
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(hql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.HQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		Long count = this.count(queryCondition);
		List<QueryClassUserBean> findList = new ArrayList<QueryClassUserBean>();

		BeanResultMapper mapper = (BeanResultMapper) ResultMapperFactory.getInstance().getMapper(MapperType.Bean);
		mapper.setResultClass(QueryClassUserBean.class);
		queryCondition.setResultMapper(mapper);

		if (queryCommond.getPage() == -1) {
			findList = (List<QueryClassUserBean>) this.findList(queryCondition);
		} else {
			int firstResult = queryCommond.calculate(count.longValue());
			findList = (List<QueryClassUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
		}
		return findList;
	}

	/**
	 * 更新审核状态
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Ningss
	 * @createDate 2013-12-30
	 */
	@Override
	public void batchAuditing(ClassUserQueryCommond queryCommond) throws Exception {
		if (TrainingConstants.SHRY.equals(queryCommond.getAuditingMsg())) {// 更新审核人员
			String hql = "update ClassUser c set c.auditingState=:auditingState,c.auditingUserDate=:auditingUserDate where c.classUserId in(:classUserIds)";
			Map<String, Object> params = new HashMap<String, Object>();
			if (PropertyUtil.objectNotEmpty(queryCommond.getAuditingState())) {
				params.put("auditingState", queryCommond.getAuditingState());
			}
			if (PropertyUtil.objectNotEmpty(queryCommond.getAuditingStateDateStr())) {
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				String dstr = queryCommond.getAuditingStateDateStr();
				Date date = sdf.parse(dstr);
				params.put("auditingUserDate", date);
			}
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassUserIds())) {
				params.put("classUserIds", queryCommond.getSearchClassUserIds());
			}
			this.executeUpdate(QueryStringType.HQL, hql, params);

			// 审核人员通过后更改培训班向干教网同步人员状态为未同步 by HuangTF At 2014-02-28
			if (queryCommond.getAuditingState().equals(ClassUser.IS_AUDITING_Y)) {
				String[] classIds = { queryCommond.getSearchClassID() };
				queryCommond.setSearchClassIds(classIds);
				this.updateGjSyncClassUserState(queryCommond);
			}
		}
		if (TrainingConstants.SHXS.equals(queryCommond.getAuditingMsg())) {// 更新审核学时
			String hql = "update ClassUser c set c.auditingHoursState=:auditingHoursState where c.classUserId in(:classUserIds)";
			Map<String, Object> params = new HashMap<String, Object>();
			if (PropertyUtil.objectNotEmpty(queryCommond.getAuditingState())) {
				params.put("auditingHoursState", queryCommond.getAuditingState());
			}
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassUserIds())) {
				params.put("classUserIds", queryCommond.getSearchClassUserIds());
			}
			this.executeUpdate(QueryStringType.HQL, hql, params);

		}
	}

	/**
	 * 统计--干部培训档案查询(别的逻辑不用调用此方法)
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @date 2014-01-08
	 */
	@Override
	public List<RecordInfoBean> findUserStudyRecordList(ClassUserQueryCommond queryCommond) throws Exception {
		// 拼写sql
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT	u.CLASS_USER_ID as classUserId,c.CLASS_ID as classId,c.CLASSNAME_YEAR as year,c.CLASS_MAIN_NAME as trainProgramName,");
		sql.append(" u.TRAINING_HOURS as hoursBig,c.ORGANIZER_NAME as trainDeptName,u.GRADUATE_STATE as assess,");
		sql.append(" c.BEGIN_DATE as beginDate,c.end_DATE as endDate");
		sql.append(" FROM training_class_user u, training_class c WHERE	1 = 1");
		sql.append(" AND c.CLASS_ID = u.CLASS_ID");
		sql.append(" AND u.USER_ID = '" + queryCommond.getSearchUserID() + "'");
		sql.append(" AND u.ACTIVE_STATE = 1 AND u.AUDITING_STATE = 1 AND c.ACTIVE_STATE = 1");
		sql.append(" AND c.AUDIT_RESULT = 1 AND c.END_STATE = 1");

		BaseQueryCondition condition = new BaseQueryCondition();
		condition.setQueryString(sql.toString());
		BeanResultMapper mapper = (BeanResultMapper) ResultMapperFactory.getInstance().getMapper(MapperType.Bean);
		mapper.setResultClass(RecordInfoBean.class);
		condition.setResultMapper(mapper);
		condition.setQueryStringType(QueryStringType.SQL);
		// 执行查询逻辑
		List<RecordInfoBean> list = (List<RecordInfoBean>) this.findList(condition, -1, -1);
		return list;
	}

	/**
	 * 统计--干部培训档案查询,查询人员信息以及任职前后学时
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @date 2014-04-01
	 */
	@Override
	public List<QueryClassUserBean> findUserInfoHoursStudyRecordList(ClassUserQueryCommond queryCommond) throws Exception {
		// 设置查询职级
		String fieldName = "";
		if (queryCommond.getSearchRank().equals("1382")) {
			fieldName = "DUTY_XCJZZ_TIME";
		} else if (queryCommond.getSearchRank().equals("1383")) {
			fieldName = "DUTY_XCJFZ_TIME";
		}

		// 先查询人员
		StringBuffer sql = new StringBuffer("");
		Map<String, Object> values = new HashMap<String, Object>();
		sql.append("select * from (");
		sql.append("select bu.user_name as userName,bb.data_name as sex,bu.birthday as birthday," + " bd.depname as depName,bu.duty_remark as position,bb_1.data_name as rank," + " bu.login_id as loginID,bu.userid as userId,bd.depid as deptId,udu.ORDER_NUM_STR as orderNum" + " from base_user bu "
				+ " LEFT JOIN base_basedata bb_1 ON bu.duty_level_code = bb_1.DATA_CODE " + " LEFT JOIN base_basedata bb ON bu.sex_code = bb.DATA_CODE," + " base_department bd," + " (select dau.USERID,dau.DEPID,dau.ORDER_NUM_STR from dept_all_user dau" + " where 1=1");
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepId())) {
			sql.append(" and dau.DEPID = :depId");
			values.put("depId", queryCommond.getSearchDepId());
		}
		sql.append(" GROUP BY dau.USERID) udu" + " where bu.userid = udu.userid and udu.depid = bd.depid");
		if (PropertyUtil.objectNotEmpty(queryCommond.getEndDateStatistics()) && PropertyUtil.objectNotEmpty(queryCommond.getDutyYear())) {
			sql.append(" and bu." + fieldName + "<DATE_SUB(:endDateStatistics,INTERVAL :dutyYear YEAR)");
			values.put("endDateStatistics", queryCommond.getEndDateStatistics());
			values.put("dutyYear", Integer.valueOf(queryCommond.getDutyYear().substring(0, 1)));
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())) {
			sql.append(" and (bu.user_name like :userName or bu.SHORT_NAME like :shortName)");
			values.put("shortName", "%" + queryCommond.getSearchUserName().replace(" ", "") + "%");
			String userName = SqlQueryUtil.changeString(queryCommond.getSearchUserName().replace(" ", ""));
			values.put("userName", userName);
		}

		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchLoginID())) {
			sql.append(" and bu.login_id = :loginId");
			values.put("loginId", queryCommond.getSearchLoginID());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepName())) {
			sql.append(" and bd.depname like :depName");
			values.put("depName", "%" + queryCommond.getSearchDepName().replace(" ", "") + "%");
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchRank())) {
			sql.append(" and bb_1.DATA_CODE = :rank");
			values.put("rank", queryCommond.getSearchRank());
		}
		sql.append(" ) as t");
		// 增加排序
		if (queryCommond.getSortInfos() != null) {
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
				sql.append(" order by " + sortInfoBean.getProperty().trim());
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
					sql.append(" " + sortInfoBean.getDirection().trim());
				}
			}
		} else {
			sql.append(" order by t.orderNum");
		}
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		Long count = this.count(queryCondition);
		List<QueryClassUserBean> findList = new ArrayList<QueryClassUserBean>();
		BeanResultMapper mapper = (BeanResultMapper) ResultMapperFactory.getInstance().getMapper(MapperType.Bean);
		mapper.setResultClass(QueryClassUserBean.class);
		queryCondition.setResultMapper(mapper);
		if (queryCommond.getPage() == -1) {
			findList = (List<QueryClassUserBean>) this.findList(queryCondition);
		} else {
			int firstResult = queryCommond.calculate(count.longValue());
			findList = (List<QueryClassUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
		}
		// 如果没有符合条件的人员，直接返回
		if (findList.size() == 0) {
			return findList;
		}
		for (QueryClassUserBean bean : findList) {
			bean.setAfterDutyTrainingHours(0d);
			bean.setBeforeDutyTrainingHours(0d);
		}
		// 设置需要查询任职前后学时的人员ID
		String[] userIds = new String[findList.size()];
		for (int i = 0; i < userIds.length; i++) {
			userIds[i] = findList.get(i).getUserId();
		}
		sql.setLength(0);// 清空查询人员语句
		values.clear();// 清空查询人员参数
		values.put("userIds", userIds);// 设置需要查询学时的人员ID
		// 任职前后时间查询学时
		if (PropertyUtil.objectNotEmpty(queryCommond.getBeforeDutyYear()) && PropertyUtil.objectNotEmpty(queryCommond.getAfterDutyYear())) {
			sql.append(this.beforeToAfterYearSql(fieldName));
			values.put("beforeDutyYear", Integer.valueOf(queryCommond.getBeforeDutyYear().substring(0, 1)));
			values.put("afterDutyYear", Integer.valueOf(queryCommond.getAfterDutyYear().substring(0, 1)));
		}
		// 任职满时间查询学时
		if (PropertyUtil.objectNotEmpty(queryCommond.getEndDateStatistics()) && PropertyUtil.objectNotEmpty(queryCommond.getDutyYear())) {
			sql.append(this.endDateStatisticsSql(fieldName));
			values.put("endDateStatistics", queryCommond.getEndDateStatistics());
			values.put("dutyYear", Integer.valueOf(queryCommond.getDutyYear().substring(0, 1)));
		}
		defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		mapper = (BeanResultMapper) ResultMapperFactory.getInstance().getMapper(MapperType.Bean);
		mapper.setResultClass(QueryClassUserBean.class);
		queryCondition.setResultMapper(mapper);

		List<QueryClassUserBean> hoursList = new ArrayList<QueryClassUserBean>();
		hoursList = (List<QueryClassUserBean>) this.findList(queryCondition);
		// 计算任职前后学时
		for (QueryClassUserBean userBean : findList) {
			for (QueryClassUserBean hoursBean : hoursList) {
				if (userBean.getUserId().equals(hoursBean.getUserId())) {
					if (PropertyUtil.objectNotEmpty(hoursBean.getHours())) {
						userBean.setBeforeDutyTrainingHours(hoursBean.getHours() - hoursBean.getBeforeDutyTrainingHours());
						userBean.setAfterDutyTrainingHours(hoursBean.getAfterDutyTrainingHours() - hoursBean.getHours());
					} else {
						userBean.setBeforeDutyTrainingHours(hoursBean.getBeforeDutyTrainingHours());
						userBean.setAfterDutyTrainingHours(hoursBean.getAfterDutyTrainingHours() - hoursBean.getBeforeDutyTrainingHours());
					}
				}
			}
		}
		return findList;

	}

	/**
	 * 统计--干部培训档案查询,任职前后时间查询学时查询语句
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @date 2014-04-01
	 */
	public String beforeToAfterYearSql(String fieldName) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT bu.USERID as userId," + " IFNULL(" + " (SELECT (IFNULL(uts.TRAINING_HOURS,0)+IFNULL(uts.ONLINE_HOURS,0)) " + " FROM user_traininghours_statistics uts WHERE uts.USERID=bu.USERID AND " + " uts.CREATE_DATE<=bu." + fieldName + " ORDER BY uts.CREATE_DATE desc LIMIT 0,1)"
				+ " ,0) as hoursBig," + " IFNULL(" + " (SELECT (uts.TRAINING_HOURS+uts.ONLINE_HOURS) " + " FROM user_traininghours_statistics uts WHERE uts.USERID=bu.USERID AND " + " uts.CREATE_DATE BETWEEN date_sub(bu." + fieldName + ", interval :beforeDutyYear YEAR) AND bu." + fieldName
				+ " ORDER BY uts.CREATE_DATE asc LIMIT 0,1)" + " ,0)" + "  as beforeDutyTrainingHoursBig," + " IFNULL(" + " (SELECT (uts.TRAINING_HOURS+uts.ONLINE_HOURS) " + " FROM user_traininghours_statistics uts WHERE uts.USERID=bu.USERID AND " + " uts.CREATE_DATE BETWEEN bu." + fieldName
				+ " AND date_add(bu." + fieldName + ", interval :afterDutyYear YEAR) " + " ORDER BY uts.CREATE_DATE desc LIMIT 0,1)" + " ,0)" + "  as afterDutyTrainingHoursBig " + " FROM base_user bu where bu.USERID in (:userIds)");
		return sql.toString();
	}

	/**
	 * 统计--干部培训档案查询,任职满时间查询学时查询语句
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @date 2014-04-01
	 */
	public String endDateStatisticsSql(String fieldName) throws Exception {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT bu.USERID as userId," + " IFNULL(" + " (SELECT" + " IFNULL(uts.TRAINING_HOURS,0)+IFNULL(uts.ONLINE_HOURS,0)" + " FROM user_traininghours_statistics uts WHERE uts.USERID=bu.userid AND uts.CREATE_DATE<bu." + fieldName + " ORDER BY uts.CREATE_DATE desc LIMIT 0,1)" + " ,0)"
				+ "as beforeDutyTrainingHoursBig," + "IFNULL(" + " (SELECT IFNULL(uts.TRAINING_HOURS,0)+IFNULL(uts.ONLINE_HOURS,0)" + " FROM user_traininghours_statistics uts WHERE uts.USERID=bu.userid AND uts.CREATE_DATE>bu." + fieldName + " ORDER BY uts.CREATE_DATE desc LIMIT 0,1)"
				+ " ,0) as afterDutyTrainingHoursBig" + " FROM base_user bu where bu.USERID in (:userIds)");
		return sql.toString();
	}

	// /**
	// * 统计--干部培训档案查询,任职前后时间查询学时查询语句
	// * @param queryCommond
	// * @return
	// * @throws Exception
	// * @author HuangTF
	// * @date 2014-04-01
	// */
	// public Map<String, Object> beforeToAfterYearSql(ClassUserQueryCommond
	// queryCommond,String fieldName) throws Exception
	// {
	// StringBuffer sql=new StringBuffer();
	// Map<String, Object> values = new HashMap<String, Object>();
	// values.put("beforeDutyYear",
	// Integer.valueOf(queryCommond.getBeforeDutyYear().substring(0, 1)));
	// values.put("afterDutyYear",
	// Integer.valueOf(queryCommond.getAfterDutyYear().substring(0, 1)));
	// sql.append("select * from (");
	// sql.append("SELECT bu.USERID as userId,bu.user_name as
	// userName,bb_1.DATA_NAME as sex,bu.birthday as birthday,"
	// +
	// " bd.DEPNAME as depName,bu.DUTY_REMARK as position,bb_2.DATA_NAME as
	// rank,bu.login_id as loginID,"
	// +
	// " (" +
	// " IFNULL(" +
	// " (SELECT (IFNULL(uts.TRAINING_HOURS,0)+IFNULL(uts.ONLINE_HOURS,0)) " +
	// " FROM user_traininghours_statistics uts WHERE uts.USERID=bu.USERID AND "
	// +
	// " uts.CREATE_DATE BETWEEN date_sub(bu."+fieldName+", interval
	// :beforeDutyYear YEAR) AND bu."+fieldName+
	// " ORDER BY uts.CREATE_DATE desc LIMIT 0,1)" +
	// " ,0)" +
	// " -IFNULL(" +
	// " (SELECT (uts.TRAINING_HOURS+uts.ONLINE_HOURS) " +
	// " FROM user_traininghours_statistics uts WHERE uts.USERID=bu.USERID AND "
	// +
	// " uts.CREATE_DATE BETWEEN date_sub(bu."+fieldName+", interval
	// :beforeDutyYear YEAR) AND bu."+fieldName+
	// " ORDER BY uts.CREATE_DATE asc LIMIT 0,1)" +
	// " ,0)" +
	// " ) as beforeDutyTrainingHoursBig," +
	// " (" +
	// " IFNULL(" +
	// " (SELECT (uts.TRAINING_HOURS+uts.ONLINE_HOURS) " +
	// " FROM user_traininghours_statistics uts WHERE uts.USERID=bu.USERID AND "
	// +
	// " uts.CREATE_DATE BETWEEN bu."+fieldName+" AND date_add(bu."+fieldName+",
	// interval :afterDutyYear YEAR) "
	// +
	// " ORDER BY uts.CREATE_DATE desc LIMIT 0,1)" +
	// " ,0)" +
	// " -IFNULL(" +
	// " (SELECT (IFNULL(uts.TRAINING_HOURS,0)+IFNULL(uts.ONLINE_HOURS,0)) " +
	// " FROM user_traininghours_statistics uts WHERE uts.USERID=bu.USERID AND "
	// +
	// " uts.CREATE_DATE BETWEEN date_sub(bu."+fieldName+", interval
	// :beforeDutyYear YEAR) AND bu."+fieldName+" "
	// +
	// " ORDER BY uts.CREATE_DATE desc LIMIT 0,1)" +
	// " ,0)" +
	// " ) as afterDutyTrainingHoursBig " +
	// " FROM base_user bu" +
	// " LEFT JOIN base_department_user bdu ON bu.USERID=bdu.USERID" +
	// " LEFT JOIN base_department bd ON bdu.DEPID=bd.DEPID" +
	// " LEFT JOIN base_basedata bb_1 ON bu.sex_code=bb_1.DATA_CODE" +
	// " LEFT JOIN base_basedata bb_2 ON bu.DUTY_LEVEL_CODE=bb_2.DATA_CODE" +
	// " where 1=1");
	// if(PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())){
	// sql.append(" and bu.user_name like :userName");
	// values.put("userName", "%"+queryCommond.getSearchUserName().replace(" ",
	// "")+"%");
	// }
	// if(PropertyUtil.objectNotEmpty(queryCommond.getSearchLoginID())){
	// sql.append(" and bu.login_id=:loginId");
	// values.put("loginId", queryCommond.getSearchLoginID());
	// }
	// if(PropertyUtil.objectNotEmpty(queryCommond.getSearchRank())){
	// sql.append(" and bu.DUTY_LEVEL_CODE=:rank");
	// values.put("rank", queryCommond.getSearchRank());
	// }
	// if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTreePath())){
	// sql.append(" and bd.TREEPATH like :treePath");
	// values.put("treePath", queryCommond.getSearchTreePath()+"%");
	// }
	// if(PropertyUtil.objectNotEmpty(queryCommond.getSearchDepName())){
	// sql.append(" and bd.DEPNAME like :depName");
	// values.put("depName", "%"+queryCommond.getSearchDepName().replace(" ",
	// "")+"%");
	// }
	// sql.append(") as t");
	// // 增加排序
	// if (queryCommond.getSortInfos() != null) {
	// SortInfoBean sortInfoBean = queryCommond.getSortInfos();
	// if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
	// sql.append(" order by " + sortInfoBean.getProperty().trim());
	// if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
	// sql.append(" " + sortInfoBean.getDirection().trim());
	// }
	// }
	// }
	// Map<String, Object> sqlValues=new HashMap<String, Object>();
	// sqlValues.put("sql", sql);
	// sqlValues.put("values", values);
	// return sqlValues;
	// }
	// /**
	// * 统计--干部培训档案查询,任职满时间查询学时查询语句
	// * @param queryCommond
	// * @return
	// * @throws Exception
	// * @author HuangTF
	// * @date 2014-04-01
	// */
	// public Map<String, Object> endDateStatisticsSql(ClassUserQueryCommond
	// queryCommond,String fieldName) throws Exception
	// {
	// StringBuffer sql=new StringBuffer();
	// Map<String, Object> values = new HashMap<String, Object>();
	// values.put("endDateStatistics", queryCommond.getEndDateStatistics());
	// values.put("dutyYear",
	// Integer.valueOf(queryCommond.getDutyYear().substring(0,1)));
	// sql.append("select * from ( ");
	// sql.append("SELECT bu.USERID as userId,bu.user_name as
	// userName,bb_1.DATA_NAME as sex,bu.birthday as birthday,"
	// +
	// " bd.DEPNAME as depName,bu.DUTY_REMARK as position,bb_2.DATA_NAME as
	// rank,bu.login_id as loginID,"
	// +
	// " IFNULL(" +
	// " (" +
	// " SELECT" +
	// " (IFNULL(uts.TRAINING_HOURS,0)+IFNULL(uts.ONLINE_HOURS,0))" +
	// " FROM user_traininghours_statistics uts WHERE uts.USERID=bu.userid AND
	// uts.CREATE_DATE<bu."+fieldName+
	// " ORDER BY uts.CREATE_DATE desc LIMIT 0,1" +
	// " ),0)" +
	// "as beforeDutyTrainingHoursBig," +
	// "( " +
	// "IFNULL(" +
	// "(" +
	// " SELECT (IFNULL(uts.TRAINING_HOURS,0)+IFNULL(uts.ONLINE_HOURS,0))" +
	// " FROM user_traininghours_statistics uts WHERE uts.USERID=bu.userid AND
	// uts.CREATE_DATE>bu."+fieldName+
	// " ORDER BY uts.CREATE_DATE desc LIMIT 0,1" +
	// " ),0)" +
	// " -" +
	// "IFNULL(" +
	// " (" +
	// " SELECT" +
	// " (IFNULL(uts.TRAINING_HOURS,0)+IFNULL(uts.ONLINE_HOURS,0))" +
	// " FROM user_traininghours_statistics uts WHERE uts.USERID=bu.userid AND
	// uts.CREATE_DATE<bu."+fieldName+
	// " ORDER BY uts.CREATE_DATE desc LIMIT 0,1" +
	// " ),0)" +
	// ")" +
	// "as afterDutyTrainingHoursBig" +
	// " FROM base_user bu" +
	// " LEFT JOIN base_department_user bdu ON bu.USERID=bdu.USERID" +
	// " LEFT JOIN base_department bd ON bdu.DEPID=bd.DEPID" +
	// " LEFT JOIN base_basedata bb_1 ON bu.sex_code=bb_1.DATA_CODE" +
	// " LEFT JOIN base_basedata bb_2 ON bu.DUTY_LEVEL_CODE=bb_2.DATA_CODE" +
	// " WHERE bu."+fieldName+"<DATE_SUB(:endDateStatistics,INTERVAL :dutyYear
	// YEAR)");
	// if(PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())){
	// sql.append(" and bu.user_name like :userName");
	// values.put("userName", "%"+queryCommond.getSearchUserName().replace(" ",
	// "")+"%");
	// }
	// if(PropertyUtil.objectNotEmpty(queryCommond.getSearchLoginID())){
	// sql.append(" and bu.login_id=:loginId");
	// values.put("loginId", queryCommond.getSearchLoginID());
	// }
	// if(PropertyUtil.objectNotEmpty(queryCommond.getSearchRank())){
	// sql.append(" and bu.DUTY_LEVEL_CODE=:rank");
	// values.put("rank", queryCommond.getSearchRank());
	// }
	// if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTreePath())){
	// sql.append(" and bd.TREEPATH like :treePath");
	// values.put("treePath", queryCommond.getSearchTreePath()+"%");
	// }
	// if(PropertyUtil.objectNotEmpty(queryCommond.getSearchDepName())){
	// sql.append(" and bd.DEPNAME like :depName");
	// values.put("depName", "%"+queryCommond.getSearchDepName().replace(" ",
	// "")+"%");
	// }
	// sql.append(") as t");
	// // 增加排序
	// if (queryCommond.getSortInfos() != null) {
	// SortInfoBean sortInfoBean = queryCommond.getSortInfos();
	// if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
	// sql.append(" order by " + sortInfoBean.getProperty().trim());
	// if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
	// sql.append(" " + sortInfoBean.getDirection().trim());
	// }
	// }
	// }
	// Map<String, Object> sqlValues=new HashMap<String, Object>();
	// sqlValues.put("sql", sql);
	// sqlValues.put("values", values);
	// return sqlValues;
	// }
	/**
	 * 查询学习档案
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @date 2014-03-26
	 */
	@Override
	public List<RecordInfoBean> findStudyRecordList(ClassUserQueryCommond queryCommond) throws Exception {
		// 拼写sql
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT	c.CLASSNAME_YEAR as year,");
		if (PropertyUtil.objectNotEmpty(queryCommond.getTrainingClassYear())) {
			sql.append(" u.TRAINING_HOURS as hoursBig,c.CLASS_MAIN_NAME as trainProgramName");
		} else {
			sql.append(" sum(u.TRAINING_HOURS) as hoursBig");
		}
		sql.append(" FROM training_class_user u, training_class c WHERE	1 = 1");
		sql.append(" AND c.CLASS_ID = u.CLASS_ID");
		sql.append(" AND u.USER_ID = '" + queryCommond.getSearchUserID() + "'");
		sql.append(" AND u.ACTIVE_STATE = 1 AND u.AUDITING_STATE = 1 AND c.ACTIVE_STATE = 1");
		sql.append(" AND c.AUDIT_RESULT = 1 AND c.END_STATE = 1");
		if (PropertyUtil.objectNotEmpty(queryCommond.getTrainingClassYear())) {
			sql.append(" and c.CLASSNAME_YEAR=" + queryCommond.getTrainingClassYear());
		} else {
			sql.append(" GROUP BY c.CLASSNAME_YEAR");
		}

		BaseQueryCondition condition = new BaseQueryCondition();
		condition.setQueryString(sql.toString());
		BeanResultMapper mapper = (BeanResultMapper) ResultMapperFactory.getInstance().getMapper(MapperType.Bean);
		mapper.setResultClass(RecordInfoBean.class);
		condition.setResultMapper(mapper);
		condition.setQueryStringType(QueryStringType.SQL);
		// 执行查询逻辑
		List<RecordInfoBean> list = (List<RecordInfoBean>) this.findList(condition, -1, -1);
		return list;
	}

	/**
	 * 更新班级上报状态
	 * 
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author NingSS
	 * @createDate 2014-1-17
	 */
	public void updateReportState(ClassUserQueryCommond commond) throws Exception {
		String hql = "update TrainingClass c set c.reportState=:reportState where c.classId = :classId";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("reportState", 2);
		params.put("classId", commond.getSearchClassID());
		this.executeUpdate(QueryStringType.HQL, hql, params);
	}

	/**
	 * 更新干教同步学时状态
	 * 
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-2-25
	 */
	@Override
	public void updateGjSyncHoursState(ClassUserQueryCommond queryCommond) throws Exception {
		StringBuffer sql = new StringBuffer("");
		sql.append("update training_class_user tcu set tcu.GJ_SYNC_HOURS_STATE = :syncState where 1=1");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("syncState", queryCommond.getGjSyncHoursState());

		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassID())) {
			sql.append(" and tcu.CLASS_ID = :classId");
			params.put("classId", queryCommond.getSearchClassID());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassUserIds())) {
			sql.append(" and tcu.CLASS_USER_ID in (:classUserId)");
			params.put("classUserId", queryCommond.getSearchClassUserIds());
		}

		this.executeUpdate(QueryStringType.SQL, sql.toString(), params);
	}

	/**
	 * 更新培训班表向干教网同步学时同步状态
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-2-27
	 */
	@Override
	public void updateGjSyncTrainingHourState(ClassUserQueryCommond queryCommond) throws Exception {
		StringBuffer sql = new StringBuffer("");
		Map<String, Object> params = new HashMap<String, Object>();
		if (!PropertyUtil.objectNotEmpty(queryCommond.getSearchClassID())) {
			sql.append("select tcu.CLASS_ID from training_class_user tcu where tcu.CLASS_USER_ID='" + queryCommond.getSearchClassUserIds()[0] + "'");
			List<String> classIds = (List<String>) this.executeFind(QueryStringType.SQL, sql.toString(), params);
			queryCommond.setSearchClassID(classIds.get(0));
		}

		params.put("classId", queryCommond.getSearchClassID());
		sql = new StringBuffer("select tcu.GJ_SYNC_HOURS_STATE from training_class_user tcu where tcu.CLASS_ID = :classId and tcu.AUDITING_STATE=" + ClassUser.IS_AUDITING_Y + " group by tcu.GJ_SYNC_HOURS_STATE");

		// List<Integer> stateList = (List<Integer>)
		// this.executeFind(QueryStringType.SQL, sql.toString(), params);
		sql = new StringBuffer("update training_class tc set tc.SYNC_TRAINING_HOURS_STATE = :syncHourState where tc.CLASS_ID =:classId");
		// if(stateList!=null&&stateList.size()==1&&stateList.get(0).intValue()==TrainingClass.IS_SYNC_TRAINING_HOURS_Y.intValue())
		// params.put("syncHourState", TrainingClass.IS_SYNC_TRAINING_HOURS_Y);
		// else
		// params.put("syncHourState", TrainingClass.IS_SYNC_TRAINING_HOURS_N);
		this.executeUpdate(QueryStringType.SQL, sql.toString(), params);
	}

	/**
	 * 更新培训班人员关系表向干教网同步人员状态
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-2-27
	 */
	@Override
	public void updateGjSyncUserState(ClassUserQueryCommond queryCommond) throws Exception {
		StringBuffer sql = new StringBuffer("");
		sql.append("update training_class_user tcu set tcu.GJ_SYNC_USER_STATE = :syncState where tcu.CLASS_ID in (:classIds)");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("syncState", queryCommond.getGjSyncUserState());
		params.put("classIds", queryCommond.getSearchClassIds());

		this.executeUpdate(QueryStringType.SQL, sql.toString(), params);
	}

	/**
	 * 更新培训班表向干教网同步人员同步状态
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-2-27
	 */
	@Override
	public void updateGjSyncClassUserState(ClassUserQueryCommond queryCommond) throws Exception {
		StringBuffer sql = new StringBuffer("");
		sql.append("update training_class tc set tc.SYNC_USER_STATE = :syncUserState where tc.CLASS_ID in (:classIds)");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("syncUserState", queryCommond.getSyncUserState());
		params.put("classIds", queryCommond.getSearchClassIds());

		this.executeUpdate(QueryStringType.SQL, sql.toString(), params);
	}

	/**
	 * 查询当前班级可以报名的职级
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-5-8
	 */
	@Override
	public String[] findClassUserRank(String classId) throws Exception {
		String sql = "select cll.LEVEL_CODE from class_level_link cll where cll.CLASS_ID='" + classId + "'";
		List<String> rankCodeList = new ArrayList<String>();
		rankCodeList = (List<String>) this.executeFind(QueryStringType.SQL, sql, null);
		String[] rankCodes = new String[rankCodeList.size()];
		rankCodeList.toArray(rankCodes);
		return rankCodes;
	}

	/**
	 * 查询当前单位所有人员，包括在当前单位任职的人员
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author HuangTF
	 * @createDate 2014-06-11
	 */
	@Override
	public List<QueryClassUserBean> findAllUserListByDepId(ClassUserQueryCommond queryCommond) throws Exception {
		StringBuffer sql = new StringBuffer("");
		Map<String, Object> values = new HashMap<String, Object>();
		sql.append("select * from (");
		sql.append(
				"select bu.user_name as userName,bb.data_name as sex,bu.birthday as birthday," + " bd.depname as depName,bu.duty_remark as position,bb_1.data_name as rank," + " bu.login_id as loginID,bu.userid as userId,bd.depid as deptId,udu.ORDER_NUM_STR as orderNum,bb_1.DATA_CODE as rankcode," +
				// 添加是否参加过中青班的查询
						"(select count(*) from training_class_user cu, training_class tc  WHERE cu.CLASS_ID = tc.CLASS_ID and cu.USER_ID = bu.userid and tc.ZQB_STATE = 1) as zqbStateBig " + " from base_user bu " + " LEFT JOIN base_basedata bb_1 ON bu.duty_level_code = bb_1.DATA_CODE "
						+ " LEFT JOIN base_basedata bb ON bu.sex_code = bb.DATA_CODE," + " base_department bd," + " (select dau.USERID,dau.DEPID,dau.ORDER_NUM_STR from dept_all_user dau" + " where 1=1");
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepId())) {
			sql.append(" and dau.DEPID = :depId");
			values.put("depId", queryCommond.getSearchDepId());
		}
		sql.append(" GROUP BY dau.USERID) udu" + " where bu.userid = udu.userid and udu.depid = bd.depid");
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())) {
			sql.append(" and (bu.user_name like :userName or bu.SHORT_NAME like :shortName)");
			values.put("shortName", "%" + queryCommond.getSearchUserName().replace(" ", "") + "%");
			String userName = SqlQueryUtil.changeString(queryCommond.getSearchUserName().replace(" ", ""));
			values.put("userName", userName);
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchLoginID())) {
			sql.append(" and bu.login_id = :loginId");
			values.put("loginId", queryCommond.getSearchLoginID());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepName())) {
			sql.append(" and bd.depname like :depName");
			values.put("depName", "%" + queryCommond.getSearchDepName().replace(" ", "") + "%");
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchRank())) {
			sql.append(" and bb_1.DATA_CODE = :rank");
			values.put("rank", queryCommond.getSearchRank());
		}
		sql.append(" ) as t");
		// 增加排序
		if (queryCommond.getSortInfos() != null) {
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
				if (sortInfoBean.getProperty().trim().equals("t.rank")) {
					sql.append(" order by t.rankcode ");
				} else if (sortInfoBean.getProperty().trim().equals("t.zqbState")) {
					sql.append(" order by t.zqbStateBig ");
				} else {

					sql.append(" order by " + sortInfoBean.getProperty().trim());
				}

				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
					sql.append(" " + sortInfoBean.getDirection().trim());
				}
			}
		} else {
			sql.append(" order by t.orderNum");
		}
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		Long count = this.count(queryCondition);
		List<QueryClassUserBean> findList = new ArrayList<QueryClassUserBean>();
		BeanResultMapper mapper = (BeanResultMapper) ResultMapperFactory.getInstance().getMapper(MapperType.Bean);
		mapper.setResultClass(QueryClassUserBean.class);
		queryCondition.setResultMapper(mapper);
		if (queryCommond.getPage() == -1) {
			findList = (List<QueryClassUserBean>) this.findList(queryCondition);
		} else {
			int firstResult = queryCommond.calculate(count.longValue());
			findList = (List<QueryClassUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
		}
		return findList;
	}

	/**
	 * 统计--干部培训档案查询,查询人员信息以及任职前后学时
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @date 2014-06-26
	 */
	@SuppressWarnings("static-access")
	@Override
	public List<QueryClassUserBean> newFindUserInfoHoursStudyRecordList(ClassUserQueryCommond queryCommond) throws Exception {
		// 设置查询职级
		String fieldName = "";
		if (queryCommond.getSearchRank().equals("1382")) {
			fieldName = "DUTY_XCJZZ_TIME";
		} else if (queryCommond.getSearchRank().equals("1383")) {
			fieldName = "DUTY_XCJFZ_TIME";
		}

		// 先查询人员
		// StringBuffer sql=new StringBuffer("");
		Map<String, Object> values = new HashMap<String, Object>();
		String sql = "";
		if (PropertyUtil.objectNotEmpty(queryCommond.getBeforeDutyYear()) && PropertyUtil.objectNotEmpty(queryCommond.getAfterDutyYear())) {
			sql = this.berAndAftSql(queryCommond, fieldName);
		} else {
			sql = this.fullyearSql(queryCommond, fieldName);
		}
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder.setBaseQueryString(sql);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		Long count = this.count(queryCondition);
		List<QueryClassUserBean> findList = new ArrayList<QueryClassUserBean>();
		BeanResultMapper mapper = (BeanResultMapper) ResultMapperFactory.getInstance().getMapper(MapperType.Bean);
		mapper.setResultClass(QueryClassUserBean.class);
		queryCondition.setResultMapper(mapper);
		if (queryCommond.getPage() == -1) {
			findList = (List<QueryClassUserBean>) this.findList(queryCondition);
		} else {
			int firstResult = queryCommond.calculate(count.longValue());
			findList = (List<QueryClassUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
		}
		return findList;
	}

	public static String berAndAftSql(ClassUserQueryCommond queryCommond, String fieldName) {
		StringBuffer buffer = new StringBuffer();
		buffer.append("select ta.userId as userId, ");
		buffer.append("ta.userName as userName, ");
		buffer.append("ta.sex as sex, ");
		buffer.append("ta.birthday as birthday, ");
		buffer.append("ta.depName as depName, ");
		buffer.append("ta.position as position, ");
		buffer.append("ta.rank as rank, ");
		buffer.append("ta.loginID as loginID, ");
		buffer.append("ta.deptId as deptId, ");
		buffer.append("ta.orderNum as orderNum, ");
		buffer.append("ta.hoursBig - ta.beforeDutyTrainingHoursBig as beforeDutyTrainingHoursBig, ");
		buffer.append("ta.afterDutyTrainingHoursBig - ta.hoursBig as afterDutyTrainingHoursBig, ");
		buffer.append("((ta.hoursBig - ta.beforeDutyTrainingHoursBig) + ");
		buffer.append("(ta.afterDutyTrainingHoursBig - ta.hoursBig)) as allStudyHoursBig, ");
		buffer.append("ta.zqbstate as zqbStateBig  ");
		buffer.append("from (SELECT bu.USERID as userId, ");
		buffer.append("bu.user_name as userName, ");
		buffer.append("bb.data_name as sex, ");
		buffer.append("bu.birthday as birthday, ");
		buffer.append("bd.depname as depName, ");
		buffer.append("bu.duty_remark as position, ");
		buffer.append("bb_1.data_name as rank, ");
		buffer.append("bu.login_id as loginID, ");
		buffer.append("bd.depid as deptId, ");
		buffer.append("udu.ORDER_NUM_STR as orderNum, ");
		buffer.append("IFNULL((SELECT sum((IFNULL(uts.TRAINING_HOURS, 0) + ");
		buffer.append("IFNULL(uts.ONLINE_HOURS, 0))) ");
		buffer.append("FROM user_traininghours_statistics uts ");
		buffer.append("WHERE uts.USERID = bu.USERID ");
		buffer.append("and uts.GETYEAR <= year(bu." + fieldName + ")), ");
		buffer.append("0) as hoursBig, ");
		buffer.append("IFNULL((SELECT sum((uts.TRAINING_HOURS + uts.ONLINE_HOURS)) ");
		buffer.append("FROM user_traininghours_statistics uts ");
		buffer.append("WHERE uts.USERID = bu.USERID ");
		buffer.append("AND uts.GETYEAR <= year(bu." + fieldName + ") - " + Integer.valueOf(queryCommond.getBeforeDutyYear().substring(0, 1)) + "), ");
		buffer.append("0) as beforeDutyTrainingHoursBig, ");
		buffer.append("IFNULL((SELECT sum((uts.TRAINING_HOURS + uts.ONLINE_HOURS)) ");
		buffer.append("FROM user_traininghours_statistics uts ");
		buffer.append("WHERE uts.USERID = bu.USERID ");
		buffer.append("AND uts.GETYEAR <= year(bu." + fieldName + ") + " + Integer.valueOf(queryCommond.getAfterDutyYear().substring(0, 1)) + "), ");
		buffer.append("0) as afterDutyTrainingHoursBig, ");
		buffer.append("(select count(*) ");
		buffer.append("from training_class_user cu, training_class tc ");
		buffer.append("WHERE cu.CLASS_ID = tc.CLASS_ID ");
		buffer.append("and cu.USER_ID = bu.userid ");
		buffer.append("and tc.ZQB_STATE = 1) as zqbstate ");
		buffer.append("FROM base_user bu ");
		buffer.append("LEFT JOIN base_basedata bb_1 ");
		buffer.append("ON bu.duty_level_code = bb_1.DATA_CODE ");
		buffer.append("LEFT JOIN base_basedata bb ");
		buffer.append("ON bu.sex_code = bb.DATA_CODE, base_department bd, ");
		buffer.append("(select dau.USERID, dau.DEPID, dau.ORDER_NUM_STR ");
		buffer.append("from dept_all_user dau ");
		buffer.append("where 1 = 1 ");

		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepId())) {
			buffer.append("and dau.DEPID = '" + queryCommond.getSearchDepId() + "' ");
		}

		buffer.append("GROUP BY dau.USERID) udu ");
		buffer.append("where bu.userid = udu.userid ");
		buffer.append("and udu.depid = bd.depid ");
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())) {
			String userName = SqlQueryUtil.changeString(queryCommond.getSearchUserName().replace(" ", ""));
			buffer.append(" and (bu.user_name like '" + userName + "' or bu.SHORT_NAME like '%" + queryCommond.getSearchUserName().replace(" ", "") + "%')");

		}

		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchLoginID())) {
			buffer.append(" and bu.login_id = " + queryCommond.getSearchLoginID() + "");
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepName())) {
			buffer.append(" and bd.depname like '%" + queryCommond.getSearchDepName().replace(" ", "") + "%'");
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchRank())) {
			buffer.append(" and bb_1.DATA_CODE = '" + queryCommond.getSearchRank() + "'");
		}

		buffer.append(")ta ");
		buffer.append("where 1 = 1 ");
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchLessTotalHours())) {
			buffer.append("and ((ta.hoursBig - ta.beforeDutyTrainingHoursBig) +(ta.afterDutyTrainingHoursBig - ta.hoursBig)) <= '" + queryCommond.getSearchLessTotalHours() + "'");
		}

		if (queryCommond.getSortInfos() != null) {
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
				if (sortInfoBean.getProperty().trim().equals("ta.zqbState")) {
					buffer.append(" order by ta.zqbStateBig ");
					if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
						buffer.append(" " + sortInfoBean.getDirection().trim());
					}
				}

			}
		} else {
			buffer.append(" order by ta.orderNum");
		}
		return buffer.toString();
	}

	public static String fullyearSql(ClassUserQueryCommond queryCommond, String fieldName) {
		StringBuffer buffer = new StringBuffer();
		buffer.append("select ta.userId as userId, ");
		buffer.append("ta.userName as userName, ");
		buffer.append("ta.sex as sex, ");
		buffer.append("ta.birthday as birthday, ");
		buffer.append("ta.depName as depName, ");
		buffer.append("ta.position as position, ");
		buffer.append("ta.rank as rank, ");
		buffer.append("ta.loginID as loginID, ");
		buffer.append("ta.deptId as deptId, ");
		buffer.append("ta.orderNum as orderNum, ");
		buffer.append("ta.beforeDutyTrainingHoursBig as beforeDutyTrainingHoursBig, ");
		buffer.append("ta.afterDutyTrainingHoursBig as afterDutyTrainingHoursBig, ");
		buffer.append("((ta.beforeDutyTrainingHoursBig) + ");
		buffer.append("(ta.afterDutyTrainingHoursBig)) as allStudyHoursBig, ");
		buffer.append("ta.zqbstate as zqbStateBig  ");
		buffer.append("from (SELECT bu.USERID as userId, ");
		buffer.append("bu.user_name as userName, ");
		buffer.append("bb.data_name as sex, ");
		buffer.append("bu.birthday as birthday, ");
		buffer.append("bd.depname as depName, ");
		buffer.append("bu.duty_remark as position, ");
		buffer.append("bb_1.data_name as rank, ");
		buffer.append("bu.login_id as loginID, ");
		buffer.append("bd.depid as deptId, ");
		buffer.append("udu.ORDER_NUM_STR as orderNum, ");
		// buffer.append("IFNULL((SELECT sum((IFNULL(uts.TRAINING_HOURS, 0) +
		// ");
		// buffer.append("IFNULL(uts.ONLINE_HOURS, 0))) ");
		// buffer.append("FROM user_traininghours_statistics uts ");
		// buffer.append("WHERE uts.USERID = bu.USERID ");
		// buffer.append("and uts.GETYEAR <= year(bu."+fieldName+")), ");
		// buffer.append("0) as hoursBig, ");
		// buffer.append("IFNULL((SELECT sum((uts.TRAINING_HOURS +
		// uts.ONLINE_HOURS)) ");
		// buffer.append("FROM user_traininghours_statistics uts ");
		// buffer.append("WHERE uts.USERID = bu.USERID ");
		// buffer.append("AND uts.GETYEAR <= year(bu."+fieldName+") -
		// "+Integer.valueOf(queryCommond.getBeforeDutyYear().substring(0,
		// 1))+"), ");
		// buffer.append("0) as beforeDutyTrainingHoursBig, ");
		// buffer.append("IFNULL((SELECT sum((uts.TRAINING_HOURS +
		// uts.ONLINE_HOURS)) ");
		// buffer.append("FROM user_traininghours_statistics uts ");
		// buffer.append("WHERE uts.USERID = bu.USERID ");
		// buffer.append("AND uts.GETYEAR <= year(bu."+fieldName+") +
		// "+Integer.valueOf(queryCommond.getAfterDutyYear().substring(0,
		// 1))+"), ");
		// buffer.append("0) as afterDutyTrainingHoursBig, ");

		buffer.append("IFNULL((SELECT sum(IFNULL(uts.TRAINING_HOURS, 0) + ");
		buffer.append("IFNULL(uts.ONLINE_HOURS, 0)) ");
		buffer.append("FROM user_traininghours_statistics uts ");
		buffer.append("WHERE uts.USERID = bu.userid ");
		buffer.append("AND uts.GETYEAR < year(bu." + fieldName + ")), ");
		buffer.append("0) as beforeDutyTrainingHoursBig, ");
		buffer.append("IFNULL((SELECT sum(IFNULL(uts.TRAINING_HOURS, 0) + ");
		buffer.append("IFNULL(uts.ONLINE_HOURS, 0)) ");
		buffer.append("FROM user_traininghours_statistics uts ");
		buffer.append("WHERE uts.USERID = bu.userid ");
		buffer.append("AND uts.GETYEAR >= YEAR(bu." + fieldName + ")), ");
		buffer.append("0) as afterDutyTrainingHoursBig, ");
		buffer.append("(select count(*) ");
		buffer.append("from training_class_user cu, training_class tc ");
		buffer.append("WHERE cu.CLASS_ID = tc.CLASS_ID ");
		buffer.append("and cu.USER_ID = bu.userid ");
		buffer.append("and tc.ZQB_STATE = 1) as zqbstate ");
		buffer.append("FROM base_user bu ");
		buffer.append("LEFT JOIN base_basedata bb_1 ");
		buffer.append("ON bu.duty_level_code = bb_1.DATA_CODE ");
		buffer.append("LEFT JOIN base_basedata bb ");
		buffer.append("ON bu.sex_code = bb.DATA_CODE, base_department bd, ");
		buffer.append("(select dau.USERID, dau.DEPID, dau.ORDER_NUM_STR ");
		buffer.append("from dept_all_user dau ");
		buffer.append("where 1 = 1 ");

		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepId())) {
			buffer.append("and dau.DEPID = '" + queryCommond.getSearchDepId() + "' ");
		}

		buffer.append("GROUP BY dau.USERID) udu ");
		buffer.append("where bu.userid = udu.userid ");
		buffer.append("and udu.depid = bd.depid ");
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())) {
			String userName = SqlQueryUtil.changeString(queryCommond.getSearchUserName().replace(" ", ""));
			buffer.append(" and (bu.user_name like '" + userName + "' or bu.SHORT_NAME like '%" + queryCommond.getSearchUserName().replace(" ", "") + "%')");
		}

		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchLoginID())) {
			buffer.append(" and bu.login_id = " + queryCommond.getSearchLoginID() + "");
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepName())) {
			buffer.append(" and bd.depname like '%" + queryCommond.getSearchDepName().replace(" ", "") + "%'");
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchRank())) {
			buffer.append(" and bb_1.DATA_CODE = '" + queryCommond.getSearchRank() + "'");
		}

		String yearAndMonth = queryCommond.getEndDateStatistics();
		Integer year = Integer.parseInt(yearAndMonth.substring(0, 4));
		Integer jyear = Integer.valueOf(queryCommond.getDutyYear().substring(0, 1));
		buffer.append(" and year(bu." + fieldName + ") <= '" + (year - jyear) + "' ");
		buffer.append(")ta ");
		buffer.append("where 1 = 1 ");
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchLessTotalHours())) {
			buffer.append("and (ta.beforeDutyTrainingHoursBig+ta.afterDutyTrainingHoursBig) <= '" + queryCommond.getSearchLessTotalHours() + "'");
		}
		// 增加排序
		if (queryCommond.getSortInfos() != null) {
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
				if (sortInfoBean.getProperty().trim().equals("ta.zqbState")) {
					buffer.append(" order by ta.zqbStateBig ");
					if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
						buffer.append(" " + sortInfoBean.getDirection().trim());
					}
				}

			}
		} else {
			buffer.append(" order by ta.orderNum");
		}

		return buffer.toString();
	}

	/**
	 * 查询班级学员人数
	 * 
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @date 2014-12-29
	 */
	@Override
	public List<ClassUserQueryBean> findStudentNum(ClassUserQueryCommond qCommond) throws Exception {
		Map<String, Object> values = new HashMap<String, Object>();
		String hql = "select count(c.classUserId) as userCountNum,c.trainingClass.classId as classId from ClassUser c where c.trainingClass.classId in(:classIds) group by c.trainingClass.classId";
		values.put("classIds", qCommond.getSearchClassIds());
		return (List<ClassUserQueryBean>) this.executeFind(QueryStringType.HQL, hql, values, ClassUserQueryBean.class);
	}

	/**
	 * 培训班学员报名情况列表
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author NingSS
	 * @createDate 2015-1-4
	 */
	@Override
	public List<QueryClassUserBean> getSelectedUserList(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> values = new HashMap<String, Object>();
		String sql = "select c.class_user_id as \"classUserId\",c.class_id as \"classId\",d.depid as \"deptId\",d.depname as \"depName\"," + " bu.userid as \"userId\",iu.user_name as \"userName\",iu.login_id as \"loginID\",c.sign_up_type as \"signUpType\",c.attendance_state as \"attendanceState\" "
				+ " from training_class_user c left join base_user bu on c.user_id = bu.userid left join interior_user iu " + " on bu.login_id = iu.login_id,base_department d, base_department_user du " + " where c.user_id = du.userid  and du.depid = d.depid and c.active_state="
				+ TrainingConstants.IS_ACTIVE_Y + " and c.auditing_state=" + ClassUser.IS_AUDITING_Y + "" + " and bu.active_state=" + TrainingConstants.IS_ACTIVE_Y + " and d.active_state=" + TrainingConstants.IS_ACTIVE_Y + " and du.active_state=" + TrainingConstants.IS_ACTIVE_Y + "";
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassID())) {
			sql += " and c.class_id =:classId";
			values.put("classId", queryCommond.getSearchClassID());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepId())) {
			sql += " and d.treepath like CONCAT((select dd.treepath from base_department dd where dd.depid = :depId), '%')";
			values.put("depId", queryCommond.getSearchDepId());
		}
		// 增加排序
		if (queryCommond.getSortInfos() != null) {
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
				sql += " order by " + sortInfoBean.getProperty().trim();
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
					sql += " " + sortInfoBean.getDirection().trim();
				}
			}
		} else {
			if (queryCommond.getType() != null && queryCommond.getType().equals("attendance")) {
				// 如果是考勤页面中的人员查询列表 则按照以下走排序 by Max at 2015-04-07
				sql += " order by c.attendance_state ,d.depname ,iu.login_id";
			} else {
				sql += " order by c.create_date desc";
			}
		}
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder.setBaseQueryString(sql);
		defaultQueryConditionBuilder.setResultObject(QueryClassUserBean.class);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		// 查询总数量
		Long count = this.count(queryCondition);
		int firstResult = queryCommond.calculate(count.longValue());
		return (List<QueryClassUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
	}

	/**
	 * 培训班单位报名情况列表
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Xupp
	 * @createDate 2015-1-8
	 */
	public List<QueryClassUserBean> getSelectedDepartmentList(ClassUserQueryCommond queryCommond) throws Exception {

		Map<String, Object> values = new HashMap<String, Object>();
		String sql = "select d.deptId as \"deptId\",d.depName as \"depName\",d.depUserNum as \"depUserNum\" from(select  d.depid as deptId,d.depname as depName, " + "nvl((select count(d.depid) from training_class_user  c,base_department_user du where c.user_id = du.userid"
				+ " and du.depid = d.depid and c.class_id = :classId group by d.depid, d.depname),0) as depUserNum " + " from base_department d where 1=1 ";
		values.put("classId", queryCommond.getSearchClassID());

		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchTreePath())) {
			sql += " and d.treepath like :treepath";
			values.put("treepath", "%" + queryCommond.getSearchTreePath() + "%");
		}
		sql += ") d";
		// 增加排序
		if (queryCommond.getSortInfos() != null) {
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
				sql += " order by " + sortInfoBean.getProperty().trim();
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
					sql += " " + sortInfoBean.getDirection().trim();
				}
			}
		} else {
			sql += " order by d.depUserNum desc";
		}
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder.setBaseQueryString(sql);
		defaultQueryConditionBuilder.setResultObject(QueryClassUserBean.class);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		// 查询总数量

		List<BigDecimal> count = (List<BigDecimal>) this.executeFind(QueryStringType.SQL, "select count(*) as count from(" + sql + ")", queryCondition.getParameters());
		queryCommond.setCount(count.get(0).longValue());
		Integer firstResult = queryCommond.calculate(count.get(0).longValue());
		return (List<QueryClassUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());

	}

	/**
	 * 培训班单位报名用户情况列表
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Xupp
	 * @createDate 2015-1-9
	 */
	public List<QueryClassUserBean> getSelectedDepartmentUserList(ClassUserQueryCommond queryCommond) throws Exception {

		Map<String, Object> values = new HashMap<String, Object>();
		/*
		 * String ssql=
		 * "select   iu.USERID as \"userId\", iu.LOGIN_ID as \"loginID\" , iu.USER_NAME as \"userName\" "
		 * + " from interior_user iu , base_department_user du " +
		 * " where iu.USERID = du.userid and du.DEPID= :depId ";
		 */
		String sql = "select   iu.USERID as \"userId\", iu.LOGIN_ID as \"loginID\" , iu.USER_NAME as \"userName\", c.sign_up_type as \"signUpType\"  " + " from training_class_user c left join base_user bu on c.user_id = bu.userid left join interior_user iu "
				+ " on bu.login_id = iu.login_id, base_department_user du " + " where c.user_id = du.userid  and du.depid = :depId and c.class_id =:classId ";
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepId())) {
			values.put("depId", queryCommond.getSearchDepId());
			values.put("classId", queryCommond.getSearchClassID());
		}
		// 增加排序
		if (queryCommond.getSortInfos() != null) {
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
				sql += " order by " + sortInfoBean.getProperty().trim();
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
					sql += " " + sortInfoBean.getDirection().trim();
				}
			}
		} else {
			sql += " order by c.create_date desc";
		}
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder.setBaseQueryString(sql);
		defaultQueryConditionBuilder.setResultObject(QueryClassUserBean.class);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		// 查询总数量
		Long count = this.count(queryCondition);
		int firstResult = queryCommond.calculate(count.longValue());
		return (List<QueryClassUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());

	}

	@Override
	public int deleteSelectedUser(ClassUserQueryCommond queryCommond) throws Exception {
		StringBuffer hql = new StringBuffer();
		hql.append("delete ClassUser c where c.user.userId  in (:userids) and c.trainingClass.classId=:classId ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("userids", queryCommond.getSearchUserIds());
		params.put("classId", queryCommond.getSearchClassID());
		super.executeUpdate(QueryStringType.HQL, hql.toString(), params);
		return 0;
	}

	/**
	 * 查询班级考勤情况
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author ShenPS
	 * @date 2015年1月11日
	 */
	public List<QueryClassUserBean> getClassAttendancerList(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> values = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer();
		sql.append("select c.class_user_id as \"classUserId\",c.class_id as \"classId\",d.depid as \"deptId\",d.depname as \"depName\",");
		sql.append(" bu.userid as \"userId\",iu.user_name as \"userName\",iu.login_id as \"loginID\", c.attendance_state as \"attendanceState\" ");
		sql.append(" from training_class_user c left join base_user bu on c.user_id = bu.userid left join interior_user iu ");
		sql.append(" on bu.login_id = iu.login_id ,base_department d, base_department_user du ");
		sql.append(" where c.user_id = du.userid  and du.depid = d.depid  ");
		sql.append("  and c.active_state=:activeState  "); // 班级人员活动状态
		sql.append(" and bu.active_state=:activeState ");// 用户活动状态
		sql.append(" and d.active_state=:activeState ");// 部门活动状态
		sql.append(" and du.active_state=:activeState ");// 部门人员活动状态
		values.put("activeState", TrainingConstants.IS_ACTIVE_Y);
		sql.append("  and c.auditing_state=:auditingState ");// 审核状态
		values.put("auditingState", ClassUser.IS_AUDITING_Y);
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassID())) {
			sql.append(" and c.class_id =:classId ");// 班级id
			values.put("classId", queryCommond.getSearchClassID());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchLoginID())) {
			sql.append(" and iu.login_id =:loginID ");// 班级id
			values.put("loginID", queryCommond.getSearchLoginID());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())) {
			sql.append(" and iu.user_name like :userName ");// 用户名
			values.put("userName", "%" + queryCommond.getSearchUserName() + "%");
		}
		// 增加排序
		if (queryCommond.getSortInfos() != null) {
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
				sql.append(" order by " + sortInfoBean.getProperty().trim());
				// sql+=" order by " + sortInfoBean.getProperty().trim();
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
					sql.append(" " + sortInfoBean.getDirection().trim());
					// sql+=" " + sortInfoBean.getDirection().trim();
				}
			}
		}
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder.setResultObject(QueryClassUserBean.class);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		// 查询总数量
		Long count = this.count(queryCondition);
		int firstResult = queryCommond.calculate(count.longValue());
		return (List<QueryClassUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
	}

	/**
	 * 批量更新考勤情况
	 * 
	 * @param queryCommond
	 * @throws Exception
	 * @author ShenPS
	 * @date 2015年1月11日
	 */
	public void updateAttendanceState(ClassUserQueryCommond queryCommond) throws Exception {
		StringBuffer sql = new StringBuffer("");
		sql.append("update training_class_user tcu set tcu.attendance_state = :attendanceState, tcu.latest_operate_date = :latestOperateDate where tcu.CLASS_USER_ID in (:classUserIds)");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("attendanceState", queryCommond.getAttendanceState());
		params.put("latestOperateDate", new Date());
		params.put("classUserIds", queryCommond.getSearchClassUserIds());
		this.executeUpdate(QueryStringType.SQL, sql.toString(), params);
	}

	/**
	 * 批量更新考勤情况
	 * 
	 * @param queryCommond
	 * @throws Exception
	 * @author ShenPS
	 * @date 2015年1月11日
	 */
	public void updateAttendanceStateByClassId(ClassUserQueryCommond queryCommond) throws Exception {
		StringBuffer sql = new StringBuffer("");
		sql.append("update training_class_user tcu set tcu.attendance_state = :attendanceState, tcu.latest_operate_date = :latestOperateDate where tcu.CLASS_ID =:classId  AND tcu.CLASS_USER_ID=:classUserId");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("attendanceState", queryCommond.getAttendanceState());
		params.put("latestOperateDate", new Date());
		params.put("classId", queryCommond.getSearchClassID());
		params.put("classUserId", queryCommond.getSearchClassUserId());

		this.executeUpdate(QueryStringType.SQL, sql.toString(), params);
	}

	/**
	 * 批量更新考勤情况
	 * 
	 * @param queryCommond
	 * @throws Exception
	 * @author ShenPS
	 * @date 2015年1月11日
	 */
	public void updateAttendanceStateByClassIdAll(ClassUserQueryCommond queryCommond) throws Exception {
		StringBuffer sql = new StringBuffer("");
		sql.append("update training_class_user tcu set tcu.attendance_state = 1, tcu.latest_operate_date = :latestOperateDate where tcu.CLASS_ID =:classId ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("latestOperateDate", new Date());
		params.put("classId", queryCommond.getSearchClassID());

		this.executeUpdate(QueryStringType.SQL, sql.toString(), params);
	}

	/**
	 * 培训班学员报名情况人数
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Max
	 * @createDate 2015-1-14
	 */
	@Override
	public Long getCountSelectedUserList(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> values = new HashMap<String, Object>();
		String sql = "select c.class_user_id as \"classUserId\",c.class_id as \"classId\",d.depid as \"deptId\",d.depname as \"depName\"," + " bu.userid as \"userId\",iu.user_name as \"userName\",iu.login_id as \"loginID\" "
				+ " from training_class_user c left join base_user bu on c.user_id = bu.userid left join interior_user iu  " + " on bu.login_id = iu.login_id,base_department d, base_department_user du,training_class tc "
				+ " where c.user_id = du.userid  and du.depid = d.depid and tc.class_id=c.class_id and c.active_state=" + TrainingConstants.IS_ACTIVE_Y + " and c.auditing_state=" + ClassUser.IS_AUDITING_Y + "" + " and bu.active_state=" + TrainingConstants.IS_ACTIVE_Y + " and d.active_state="
				+ TrainingConstants.IS_ACTIVE_Y + " and du.active_state=" + TrainingConstants.IS_ACTIVE_Y + "";

		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassID())) {
			sql += " and c.class_id =:classId";
			values.put("classId", queryCommond.getSearchClassID());
		}

		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepId())) {
			sql += " and d.treepath like CONCAT((select dd.treepath from base_department dd where dd.depid = :depId), '%')";
			values.put("depId", queryCommond.getSearchDepId());
		}

		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchSignUpYear())) {
			sql += " and c.signup_year='" + queryCommond.getSearchSignUpYear() + "'";
		}

		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassCategory())) {
			if (queryCommond.getSearchClassCategory().equals("2")) {
				sql += " and tc.CLASS_TYPE='gfb'";
			} else {
				sql += " and tc.CLASS_TYPE!='gfb'";
			}

		}

		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder.setBaseQueryString(sql);
		defaultQueryConditionBuilder.setResultObject(QueryClassUserBean.class);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		// 查询总数量
		Long count = this.count(queryCondition);
		return count;
	}

	/**
	 * 人员报名待选列表
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author ShenPS
	 * @date 2015年1月23日
	 */
	public List<QueryClassUserBean> getToChooseUserList(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> values = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer();
		sql.append(" select iu.login_id as \"loginID\",iu.user_name as \"userName\" ,d.depname as \"depName\" ");
		sql.append(" from base_department_user du, base_department d, base_user iu ");
		sql.append(" where iu.userid=du.userid");
		sql.append("  and du.depid=d.depid ");
		sql.append(" and iu.userid not in (select cu.user_id from training_class_user cu where  cu.signup_year=(select c.classname_year from training_class c where c.class_id=:classId) and cu.active_state=:activeState and cu.auditing_state=:auditingState)");
		values.put("classId", queryCommond.getSearchClassID());
		values.put("activeState", ClassUser.IS_ACTIVE_STATE_Y);
		values.put("auditingState", ClassUser.IS_AUDITING_Y);
		values.put("treepath", queryCommond.getSearchClassID());
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchTreePath())) {
			sql.append(" and d.treepath like :treepath ");
			values.put("treepath", queryCommond.getSearchTreePath() + "%");
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchLoginID())) {
			sql.append(" and iu.login_id=:loginID ");
			values.put("loginID", queryCommond.getSearchLoginID());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())) {
			sql.append(" and iu.user_name like :userName ");
			values.put("userName", "%" + queryCommond.getSearchUserName() + "%");
		}
		// 拼写排序的sql begin
		List<SortInfoBean> sortInfoList = queryCommond.getSortInfo();
		if (sortInfoList != null && sortInfoList.size() > 0) {
			SortInfoBean sortInfo = sortInfoList.get(0);
			sql.append(" order by " + PropertyUtil.sqlInjectionFilter(sortInfo.getProperty()) + " " + PropertyUtil.sqlInjectionFilter(sortInfo.getDirection()));
		}
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder.setResultObject(QueryClassUserBean.class);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		// 查询总数量
		Long count = this.count(queryCondition);
		int firstResult = queryCommond.calculate(count.longValue());
		List<QueryClassUserBean> resultList = (List<QueryClassUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
		return resultList;
	}

	/**
	 * 班级人员列表
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author ShenPS
	 * @date 2015年1月27日
	 */
	public List<QueryClassUserBean> findTrainingClassUserListNoPage(TrainingClassQueryCommond queryCommond) throws Exception {
		Map<String, Object> values = new HashMap<String, Object>();
		// 拼写查询的sql
		StringBuffer sql = new StringBuffer();
		// sql需要关联报名人员姓名表 interior_user查询这个表中的人员姓名 by Max at 2015-2-28
		sql.append(
				" select cu.user_id as \"userId\", iu.user_name as \"userName\",  c.class_name as \"className\", c.training_dept_name as \"trainingDeptName\",  c.class_num as \"classNum\", d.depname as \"depName\" , u.login_id as \"loginID\",d.TREEPATH as \"treePath\",cu.attendance_state as \"attendanceState\" ");
		sql.append("  from training_class_user cu left join training_class c  on cu.class_id = c.class_id, base_user u,base_department_user du,base_department d,interior_user iu");
		sql.append("   where cu.user_id = u.userid ");
		sql.append("  and du.userid=u.userid ");
		sql.append("  and du.depid=d.depid and iu.userid=u.userid");
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassIdList())) {
			sql.append(" and cu.class_id in(:classIds)");
			values.put("classIds", queryCommond.getSearchClassIdList());
		}
		// 如果 是单位查询 则查询这个管理 范围下的人员 by Max at 2015-02-17
		if (queryCommond.getSearchTrainingApplyDeptTreepath() != null && !"".equals(queryCommond.getSearchTrainingApplyDeptTreepath())) {
			sql.append(" and d.TREEPATH like :treepath ");
			values.put("treepath", "%" + queryCommond.getSearchTrainingApplyDeptTreepath() + "%");
		}
		if (queryCommond.getSearchType() != null && queryCommond.getSearchType().equals("attedence")) {
			// 考勤查询人员列表的排序不同 ,按全勤、非全勤、未出勤（请假）、未出勤（未请假）做第一排序，再按单位做第二排序，再按登录号做第三排序
			// by Max at 2015-04-07
			sql.append(" order by cu.attendance_state,d.depname,u.login_id");
		} else {
			sql.append(" order by c.class_id ");
		}
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(QueryClassUserBean.class);
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition1 = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition1.setParameters(values);
		return (List<QueryClassUserBean>) this.findList(queryCondition1);
	}

	@Override
	public List<QueryClassUserBean> getSelectedUserListAttedence(ClassUserQueryCommond queryCommond) throws Exception {

		Map<String, Object> values = new HashMap<String, Object>();
		String sql = "select c.class_user_id as \"classUserId\",c.class_id as \"classId\",c.ATTENDANCE_STATE as \"attendanceState\",d.depid as \"deptId\",d.depname as \"depName\"," + " bu.userid as \"userId\",iu.user_name as \"userName\",iu.login_id as \"loginID\",c.sign_up_type as \"signUpType\" "
				+ " from training_class_user c left join base_user bu on c.user_id = bu.userid left join interior_user iu " + " on bu.login_id = iu.login_id,base_department d, base_department_user du " + " where c.user_id = du.userid  and du.depid = d.depid and c.active_state="
				+ TrainingConstants.IS_ACTIVE_Y + " and c.auditing_state=" + ClassUser.IS_AUDITING_Y + "" + " and bu.active_state=" + TrainingConstants.IS_ACTIVE_Y + " and d.active_state=" + TrainingConstants.IS_ACTIVE_Y + " and du.active_state=" + TrainingConstants.IS_ACTIVE_Y + "";
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassID())) {
			sql += " and c.class_id =:classId";
			values.put("classId", queryCommond.getSearchClassID());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepId())) {
			sql += " and d.treepath like CONCAT((select dd.treepath from base_department dd where dd.depid = :depId), '%')";
			values.put("depId", queryCommond.getSearchDepId());
		}
		// 学员姓名
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())) {
			sql += " and iu.user_name like :userName ";
			values.put("userName", "%" + queryCommond.getSearchUserName().trim() + "%");
		}

		// 登陆id
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchLoginID())) {
			sql += " and iu.login_id like :loginId  ";
			values.put("loginId", "%" + queryCommond.getSearchLoginID().trim() + "%");
		}

		// 单位名称
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepName())) {
			sql += " and d.depname like :depname ";
			values.put("depname", "%" + queryCommond.getSearchDepName().trim() + "%");
		}

		// 考勤状态
		if (PropertyUtil.objectNotEmpty(queryCommond.getAttendanceState())) {
			sql += " and c.ATTENDANCE_STATE  =:attendanceState ";
			values.put("attendanceState", queryCommond.getAttendanceState());
		}
		// 增加排序
		if (queryCommond.getSortInfos() != null) {
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
				sql += " order by " + sortInfoBean.getProperty().trim();
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
					sql += " " + sortInfoBean.getDirection().trim();
				}
			}
		} else {
			sql += " order by c.create_date desc";
		}
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder.setBaseQueryString(sql);
		defaultQueryConditionBuilder.setResultObject(QueryClassUserBean.class);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		// 查询总数量
		Long count = this.count(queryCondition);
		int firstResult = queryCommond.calculate(count.longValue());
		return (List<QueryClassUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());

	}

	/**
	 * 按考勤状态查询班级人员列表
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author LongJZ
	 * @createDate 2015-3-26
	 */
	@Override
	public List<QueryClassUserBean> findClassUserListByAttendanceState(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> values = new HashMap<String, Object>();
		String sql = "select c.auditing_State as \"auditingState\"," + "bu.login_id as \"loginID\"," + "bu.user_name as \"userName\" ," + "sex.data_name as \"sex\"," + "bu.BIRTHDAY   as \"birthday\", " + "folk.data_name as \"folkName\","
				+ (queryCommond.getIsSuperBusinessAdmin() ? "ld.depname" : "d.depname") + " as \"depName\"," + "bu.DUTY_REMARK as \"dutyRemark\"," + "bbb.data_name as \"rank\" " + " from training_class_user c " + "left join base_user bu on c.user_id = bu.userid "
				+ "left join base_basedata sex on sex.data_code = bu.sex_code " + "left join base_basedata folk on folk.data_code = bu.folk_code " + "left join base_basedata bbb on bu.duty_level_code = bbb.data_code " + "left join base_department_user du on du.userid=bu.userid "
				+ "left join base_department d on du.depid=d.depid " + "left join base_department ld on du.level_depid=ld.depid"

				+ " where c.user_id = du.userid  and du.depid = d.depid and c.active_state=" + TrainingConstants.IS_ACTIVE_Y + " and bu.active_state=" + TrainingConstants.IS_ACTIVE_Y + " and d.active_state=" + TrainingConstants.IS_ACTIVE_Y + " and du.active_state=" + TrainingConstants.IS_ACTIVE_Y
				+ "";
		// 审核状态查询
		sql += " and c.auditing_State in (1,5)";
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassID())) {
			sql += " and c.class_id = :classId";
			values.put("classId", queryCommond.getSearchClassID());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepId())) {
			sql += " and d.treepath like CONCAT((select dd.treepath from base_department dd where dd.depid = :depId), '%')";
			values.put("depId", queryCommond.getSearchDepId());
		}

		// if(PropertyUtil.objectNotEmpty(queryCommond.getAttendanceState())){
		// sql+=" and c.attendance_state = :attendanceState";
		// values.put("attendanceState", queryCommond.getAttendanceState());
		// }
		// 增加登陆号查询 by Max at 2015-4-3
		/*
		 * if (PropertyUtil.objectNotEmpty(queryCommond.getSearchLoginID())) {
		 * sql += " and bu.login_id = :loginId"; values.put("loginId",
		 * queryCommond.getSearchLoginID()); }
		 */
		// 增加姓名查询 by Max at 2015-4-3
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())) {
			sql += " and bu.user_name like :username";
			values.put("username", "%" + queryCommond.getSearchUserName() + "%");
		}
		// 增加单位名称查询 by Max at 2015-4-3
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepName())) {
			sql += " and d.depname like :depname";
			values.put("depname", "%" + queryCommond.getSearchDepName() + "%");
		}
		// 单位及职务查询
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUnit())) {
			sql += " and bu.duty_remark like :position";
			values.put("position", "%" + queryCommond.getSearchUnit() + "%");
		}

		// 增加排序
		if (queryCommond.getSortInfos() != null) {
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
				sql += " order by " + sortInfoBean.getProperty().trim();
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
					sql += " " + sortInfoBean.getDirection().trim();
				}
			}
		} else {
			// sql+=" order by d.depname,bu.login_id ";
			sql += " order by d.customfield6,bbb.order_num,bu.login_id";
		}
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder.setBaseQueryString(sql);
		defaultQueryConditionBuilder.setResultObject(QueryClassUserBean.class);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		// 查询总数量
		Long count = this.count(queryCondition);
		int firstResult = queryCommond.calculate(count.longValue());
		return (List<QueryClassUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
	}

	/**
	 * 查询报名人员
	 * 
	 * @param queryCommond
	 * @return List<QueryClassUserBean>
	 * @throws Exception
	 * @author RongSL
	 * @createDate 2016-12-23
	 */
	@Override
	public List<QueryClassUserBean> findClassUserListNoSignUp(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> values = new HashMap<String, Object>();
		String sql = "select distinct concat(d.CUSTOMFIELD6,'') as \"CUSTOMFIELD6\",bbb.order_num as \"CUSTOMFIELD7\", " + "bb.order_num as  \"CUSTOMFIELD8\", bt.order_num as \"CUSTOMFIELD9\",c.auditing_State as \"auditingState\","
				+ "bd.depname AS \"depParentName\",bbb.data_name \"rank\",c.class_user_id as \"classUserId\"," + "c.class_id as \"classId\",d.depid as \"deptId\",c.ATTENDANCE_STATE as \"attendanceState\",bu.PAPER_NUM \"paperNum\",";
		if (queryCommond.getIsSuperBusinessAdmin())
			sql += "ld.depname";
		else
			sql += "d.depname";
		sql += " as \"depName\",CONCAT(bu.userid,'') as \"userId\",c.study_id \"attachmentID\",c.upload_date \"uploadDate\"," + "bu.user_name as \"userName\",bu.login_id as \"loginID\",c.sign_up_type as \"signUpType\",bb.data_name as \"sex\","
				+ "bu.birthday as \"birthday\",bu.duty_remark as \"position\",bu.mobile_Telephone as \"mobileTelephone\",bt.data_name as \"folkName\",c.remark as \"remark\" " + " from training_class_user c," + SystemConstants.SYSTEM_CODE
				+ "uum_user bu left join base_basedata bbb on bu.duty_Level_Code = bbb.data_code " + "LEFT JOIN base_basedata bb ON bu.sex_code = bb.DATA_CODE LEFT JOIN base_basedata bt ON bu.folk_Code = bt.DATA_CODE,"+ SystemConstants.SYSTEM_CODE + "uum_department d left join "+ SystemConstants.SYSTEM_CODE + "uum_department bd on d.parent_id=bd.depid, "
				+ SystemConstants.SYSTEM_CODE + "uum_department_user  du ";
		if (queryCommond.getIsSuperBusinessAdmin())
			sql += "left join "+ SystemConstants.SYSTEM_CODE + "uum_department ld on du.level_depid = ld.depid";
		sql += " where c.user_id = bu.userid and bu.userid = du.userid and d.depid = du.depid and" + " c.active_state=" + TrainingConstants.IS_ACTIVE_Y + " and bu.active_state=" + TrainingConstants.IS_ACTIVE_Y + " and du.active_state=" + TrainingConstants.IS_ACTIVE_Y + " and du.active_state="
				+ TrainingConstants.IS_ACTIVE_Y + "";
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassID())) {
			sql += " and c.class_id = :classId";
			values.put("classId", queryCommond.getSearchClassID());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepId())) {
			sql += " and d.treepath like CONCAT((select dd.treepath from base_department dd where dd.depid = :depId), '%')";
			values.put("depId", queryCommond.getSearchDepId());
		}
		/*
		 * if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepId())) { sql
		 * += " and t.DEPID =:depId "; values.put("depId",
		 * queryCommond.getSearchDepId()); }
		 */
		// if(PropertyUtil.objectNotEmpty(queryCommond.getAttendanceState())){
		// sql+=" and c.attendance_state = :attendanceState";
		// values.put("attendanceState", queryCommond.getAttendanceState());
		// }
		// 增加登陆号查询 by Max at 2015-4-3
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchLoginID())) {
			sql += " and bu.login_id = :loginId";
			values.put("loginId", queryCommond.getSearchLoginID());
		}
		// 增加姓名查询 by Max at 2015-4-3
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())) {
			sql += " and bu.user_name like :username";
			values.put("username", "%" + queryCommond.getSearchUserName() + "%");
		}
		// 增加ID查询
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserID())) {
			sql += " and bu.userid =:userId";
			values.put("userId", queryCommond.getSearchUserID());
		}
		// 电话查询
		if (PropertyUtil.objectNotEmpty(queryCommond.getMobileTelephone())) {
			sql += " and c.mobile_Telephone = :phone";
			values.put("phone", queryCommond.getMobileTelephone());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchNoUserIds()) && PropertyUtil.objectNotEmpty(queryCommond.getSearchNoUserIds()[0])) {
			sql += "  and bu.userid not in (:noUserIds) ";
			values.put("noUserIds", queryCommond.getSearchNoUserIds());
		}
		// 增加单位名称查询 by Max at 2015-4-3
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepName())) {
			if (queryCommond.getIsSuperBusinessAdmin())
				sql += " and ld.depname like :depname";
			else
				sql += " and d.depname like :depname";
			values.put("depname", "%" + queryCommond.getSearchDepName() + "%");
		}
		// 单位及职务查询
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUnit())) {
			sql += " and bu.duty_remark like :position";
			values.put("position", "%" + queryCommond.getSearchUnit() + "%");
		}
		// 审核状态查询
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchSignUpState())) {
			sql += " and c.auditing_State = :auditingState";
			values.put("auditingState", queryCommond.getSearchSignUpState());
		}

		// 查询民族是否为空
		if (PropertyUtil.objectNotEmpty(queryCommond.getIsNullByNation())) {
			sql += " and bt.data_name is null ";
		}
		// 查询上传了附件的学员
		if (PropertyUtil.objectNotEmpty(queryCommond.getIsUpload())) {
			sql += " and c.study_id is not null ";
		}

		sql += " and (bu.LOGIN_ID not like '9%') and bu.USER_NAME <> '通讯员' and (bu.USER_NAME not like '%管理员') ";

		// 增加排序
		if (queryCommond.getSortInfos() != null) {
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
				sql += " order by " + sortInfoBean.getProperty().trim();
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
					sql += " " + sortInfoBean.getDirection().trim();
				}
			}
		} else {
			sql += " order by d.CUSTOMFIELD6,bbb.order_num,bu.login_id";
		}
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder.setBaseQueryString(sql);
		defaultQueryConditionBuilder.setResultObject(QueryClassUserBean.class);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		// 查询总数量
		// Long count = this.count(queryCondition);
		int count = this.executeFind(QueryStringType.SQL, sql, values).size();
		int firstResult = queryCommond.calculate(count);
		return (List<QueryClassUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
	}

	/**
	 * 查询没有在培训班中报名的学员，如果确实没有报名那么查出学员详情
	 * 
	 * @param queryCommond
	 * @return List<QueryClassUserBean>
	 * @throws Exception
	 * @author RongSL 2017年12月29日
	 */
	@Override
	public List<QueryClassUserBean> findClassUserNoSignUpDetails(ClassUserQueryCommond queryCommond) throws Exception {
		StringBuffer sb = new StringBuffer("SELECT u.USERID \"userId\", u.LOGIN_ID \"loginID\", u.USER_NAME \"userName\",u.PAPER_NUM \"paperNum\", " 
				+ "u.SEX_CODE \"sexCode\", u.DUTY_REMARK \"position\", u.DUTY_LEVEL_CODE \"rankCode\", u.BIRTHDAY \"birthday\",ud.depname \"depName\", "
				+ "u.FOLK_CODE \"folkCode\", u.MOBILE_TELEPHONE \"mobileTelephone\" FROM  "+SystemConstants.SYSTEM_CODE+"uum_user u "
				+ "left join "+SystemConstants.SYSTEM_CODE+"uum_department_user udu on u.USERID=udu.userid left join  "+SystemConstants.SYSTEM_CODE+"uum_department ud on udu.depid=ud.depid WHERE " 
				+ "u.USERID NOT IN ( SELECT tcu.user_id FROM training_class_user tcu WHERE tcu.class_id =:classId AND tcu.user_id =:userId ) AND u.USERID =:userId");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("userId", queryCommond.getSearchUserID());
		params.put("classId", queryCommond.getSearchClassID());
		return (List<QueryClassUserBean>) this.executeFind(QueryStringType.SQL, sb.toString(), params, QueryClassUserBean.class);
	}

	/**
	 * 查询未报名人员(归档数据)
	 * @param queryCommond
	 * @return List<QueryClassUserBean>
	 * @throws Exception
	 * @author RongSL
	 * @createDate 2016-12-23
	 */
	@Override
	public List<QueryClassUserBean> findNoClassUserListByPxarchives(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> values = new HashMap<String, Object>();
		String sql = "select "
				+ "t.depid as \"deptId\","
				+ "c.shiguan as \"shiguan\", t.depname  as \"depName\",c.userid as \"userId\",c.user_name as \"userName\",c.login_id as \"loginID\","
				+ "c.birthday as \"birthday\",c.duty_remark as \"position\","
				+ "c.mobile_Telephone as \"phone\" " 
				+ " from " + SystemConstants.SYSTEM_CODE +"uum_user c "
				+ " left join " + SystemConstants.SYSTEM_CODE +"uum_sys_dep_sysrole_grp_view usds on c.userid = usds.userid"
				+ " left join " + SystemConstants.SYSTEM_CODE +"uum_department_user du on c.userid = du.userid"
				+ " left join " + SystemConstants.SYSTEM_CODE +"uum_department t on t.depid = du.depid where usds.userid IS NULL ";
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassID())) {
			sql += " and c.userid not in ( select tcu.USER_ID from " + SystemConstants.SYSTEM_CODE +"training_class_user_pxarchives tcu where tcu.class_id=:classId) ";
			values.put("classId", queryCommond.getSearchClassID());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchTreePath())) {
			sql += " and t.treepath like :treepath";
			values.put("treepath", queryCommond.getSearchTreePath() + "%");
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchLoginID())) {
			sql += " and c.login_id=:loginId ";
			values.put("loginId", queryCommond.getSearchLoginID());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepName())) {
			sql += " and t.depname like :depName";
			values.put("depName","%"+ queryCommond.getSearchDepName() + "%");
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())) {
			sql += " and c.user_name like :userName";
			values.put("userName", "%"+queryCommond.getSearchUserName() + "%");
		}

		// 增加排序
		if (queryCommond.getSortInfos() != null) {
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
				sql += " order by " + sortInfoBean.getProperty().trim();
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
					sql += " " + sortInfoBean.getDirection().trim();
				}
			}
		} else {
			sql += " order by "
//					+ "t.CUSTOMFIELD6,c.duty_level_code,"
					+ "c.login_id";
		}
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder.setBaseQueryString(sql);
		defaultQueryConditionBuilder.setResultObject(QueryClassUserBean.class);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		// 查询总数量
		Long count = this.count(queryCondition);
		int firstResult = queryCommond.calculate(count);
		return (List<QueryClassUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
	}
	
	/**
	 * 查询报名人员(归档数据)
	 * @param queryCommond
	 * @return List<QueryClassUserBean>
	 * @throws Exception
	 * @author RongSL
	 * @createDate 2016-12-23
	 */
	@Override
	public List<QueryClassUserBean> findClassUserListByPxarchives(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> values = new HashMap<String, Object>();
		String sql = "select c.auditing_State as \"auditingState\",bbb.data_name \"rank\",c.class_user_id as \"classUserId\"," + "c.class_id as \"classId\",t.depid as \"deptId\",c.attendance_state as \"attendanceState\","
				+ "c.shiguan as \"shiguan\",c.training_Hours as \"trainingHours\",c.lock_State as \"lockState\"," + "c.doubtful as \"doubtful\",c.gb_State as \"gbState\", t.depname  as \"depName\"," + " bu.userid as \"userId\",c.user_name as \"userName\",bu.login_id as \"loginID\","
				+ "c.sign_up_type as \"signUpType\",bb.data_name as \"sex\",c.birthday as \"birthday\"," + "c.duty_remark as \"position\",c.mobile_Telephone as \"phone\",bt.data_name as \"folkName\",c.remark as \"remark\" " + " from " + SystemConstants.SYSTEM_CODE
				+ "training_class_user_pxarchives c left join base_basedata bb ON c.sex_code = bb.data_code " + "left join base_basedata bt on c.folk_code = bt.data_code, " + SystemConstants.SYSTEM_CODE + "uum_user bu left join base_basedata bbb on bu.duty_Level_Code= bbb.data_code, "
				+ SystemConstants.SYSTEM_CODE + "uum_department t, " + SystemConstants.SYSTEM_CODE + "uum_department_user du where c.user_id = bu.userid and c.user_id = du.userid and t.depid = du.depid ";
		// + " and c.active_state=" + TrainingConstants.IS_ACTIVE_Y ;
		// " and bu.active_state=" + TrainingConstants.IS_ACTIVE_Y +
		// " and du.active_state=" + TrainingConstants.IS_ACTIVE_Y +
		// " and du.active_state=" + TrainingConstants.IS_ACTIVE_Y + "";
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassID())) {
			sql += " and c.class_id = :classId";
			values.put("classId", queryCommond.getSearchClassID());
		}
		// if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepId())) {
		// sql += " and t.treepath like CONCAT((select dd.treepath from
		// base_department dd where dd.depid = :depId), '%')";
		// values.put("depId", queryCommond.getSearchDepId());
		// }
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchTreePath())) {
			sql += " and t.treepath like :treepath";
			values.put("treepath", queryCommond.getSearchTreePath() + "%");
		}
		/*
		 * if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepId())) { sql
		 * += " and t.DEPID =:depId "; values.put("depId",
		 * queryCommond.getSearchDepId()); }
		 */
		if (queryCommond.getSearchLockState() != null) {
			sql += " and c.lock_state = :lockState";
			values.put("lockState", queryCommond.getSearchLockState());
		}
		// 增加登陆号查询 by Max at 2015-4-3
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchLoginID())) {
			sql += " and bu.login_id like :loginId";
			values.put("loginId", "%" + queryCommond.getSearchLoginID() + "%");
		}
		// 增加姓名查询 by Max at 2015-4-3
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())) {
			sql += " and bu.user_name like :username";
			String userName = SqlQueryUtil.changeString(queryCommond.getSearchUserName().replace(" ", ""));
			values.put("username", userName);
		}
		// 增加单位名称查询 by Max at 2015-4-3
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepName())) {
			// if (queryCommond.getIsSuperBusinessAdmin())
			// sql += " and ld.depname like :depname";
			// else
			sql += " and t.depname like :depname";
			values.put("depname", "%" + queryCommond.getSearchDepName() + "%");
		}
		// 单位及职务查询
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUnit())) {
			sql += " and bu.duty_remark like :position";
			values.put("position", "%" + queryCommond.getSearchUnit() + "%");
		}
		// 审核状态查询
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchSignUpState())) {
			sql += " and c.auditing_State = :auditingState";
			values.put("auditingState", queryCommond.getSearchSignUpState());
		} else {
			sql += " and c.auditing_State in (1,2,3) ";
		}
		
		// 查询民族是否为空
		if (PropertyUtil.objectNotEmpty(queryCommond.getIsNullByNation())) {
			sql += " and bt.data_name is null ";
		}
		
		// sql += " and (bu.LOGIN_ID not like '9%') and bu.USER_NAME <> '通讯员'
		// and (bu.USER_NAME not like '%管理员') ";
		
		// 增加排序
		if (queryCommond.getSortInfos() != null) {
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
				sql += " order by " + sortInfoBean.getProperty().trim();
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
					sql += " " + sortInfoBean.getDirection().trim();
				}
			}
		} else {
			// sql+=" order by d.depname,bu.login_id ";
			sql += " order by t.CUSTOMFIELD6,bu.duty_level_code,bu.login_id";
		}
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder.setBaseQueryString(sql);
		defaultQueryConditionBuilder.setResultObject(QueryClassUserBean.class);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		// 查询总数量
		Long count = this.count(queryCondition);
		int firstResult = queryCommond.calculate(count);
		return (List<QueryClassUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
	}

	/**
	 * 查询班级考核人员
	 * 
	 * @param queryCommond
	 * @return List<QueryClassUserBean>
	 * @throws Exception
	 * @author RongSL
	 * @createDate 2017-02-22
	 */
	@Override
	public List<QueryClassUserBean> findClassUserListExaminationList(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> values = new HashMap<String, Object>();
		String sql = "select distinct d.CUSTOMFIELD6 as \"CUSTOMFIELD6\",bbb.order_num  as \"CUSTOMFIELD7\", bb.order_num as  \"CUSTOMFIELD8\", bt.order_num as \"CUSTOMFIELD9\",c.auditing_State as \"auditingState\",bd.depname AS \"depParentName\",bbb.data_name \"rank\",c.class_user_id as \"classUserId\",c.class_id as \"classId\",d.depid as \"deptId\",c.ATTENDANCE_STATE as \"attendanceState\",";
		if (queryCommond.getIsSuperBusinessAdmin())
			sql += "ld.depname";
		else
			sql += "d.depname";
		sql += " as \"depName\",bu.userid as \"userId\",bu.user_name as \"userName\",bu.login_id as \"loginID\",c.sign_up_type as \"signUpType\",c.examination_remark as \"examinationRemark\",ifnull(c.examination_result,'1356') as \"examinationResult\",ifnull(tc.lock_state,2) as \"lockState\","
				+ "bb.data_name as \"sex\",bu.birthday as \"birthday\",bu.duty_remark as \"position\",bu.mobile_Telephone as \"mobileTelephone\",bt.data_name as \"folkName\",c.remark as \"remark\",ifnull(c.training_hours,tc.training_hour) as \"trainingHour\"  "
				+ " from training_class_user c left join training_class tc on tc.class_id=c.class_id left join " + SystemConstants.SYSTEM_CODE
				+ "uum_user bu  on c.user_id = bu.userid  left join base_basedata bbb on bu.duty_Level_Code = bbb.data_code LEFT JOIN base_BASEDATA bb ON bu.sex_code = bb.DATA_CODE LEFT JOIN base_BASEDATA bt ON bu.folk_Code = bt.DATA_CODE," + " " + SystemConstants.SYSTEM_CODE
				+ "uum_department d left join " + SystemConstants.SYSTEM_CODE + "uum_department bd on d.parent_id=bd.depid, " + SystemConstants.SYSTEM_CODE + "uum_department_user  du ";
		if (queryCommond.getIsSuperBusinessAdmin())
			sql += "left join " + SystemConstants.SYSTEM_CODE + "uum_department ld on du.level_depid = ld.depid";
		sql += " where c.user_id = du.userid and d.depid = du.depid and" + " c.active_state=" + TrainingConstants.IS_ACTIVE_Y + " and bu.active_state=" + TrainingConstants.IS_ACTIVE_Y + " and du.active_state=" + TrainingConstants.IS_ACTIVE_Y + " and du.active_state=" + TrainingConstants.IS_ACTIVE_Y
				+ "";
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassID())) {
			sql += " and c.class_id = :classId ";
			values.put("classId", queryCommond.getSearchClassID());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepId())) {
			sql += " and d.treepath like CONCAT((select dd.treepath from " + SystemConstants.SYSTEM_CODE + "uum_department dd where dd.depid = :depId), '%')";
			values.put("depId", queryCommond.getSearchDepId());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchExaminationResultType())) {
			if (queryCommond.getSearchExaminationResultType().equals("1356")) {
				sql += " and (c.examination_result=:result or c.examination_result is null) ";
			} else {
				sql += " and c.examination_result=:result ";
			}
			values.put("result", queryCommond.getSearchExaminationResultType());
		}
		/*
		 * if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepId())) { sql
		 * += " and t.DEPID =:depId "; values.put("depId",
		 * queryCommond.getSearchDepId()); }
		 */
		// if(PropertyUtil.objectNotEmpty(queryCommond.getAttendanceState())){
		// sql+=" and c.attendance_state = :attendanceState";
		// values.put("attendanceState", queryCommond.getAttendanceState());
		// }
		// 增加登陆号查询 by Max at 2015-4-3
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchLoginID())) {
			sql += " and bu.login_id = :loginId";
			values.put("loginId", queryCommond.getSearchLoginID());
		}
		// 增加姓名查询 by Max at 2015-4-3
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())) {
			sql += " and replace(bu.user_name,'　','') like :username";
			values.put("username", "%" + queryCommond.getSearchUserName().replace(" ", "%").replace("　", "%").replace("	", "%") + "%");
		}
		// 增加单位名称查询 by Max at 2015-4-3
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepName())) {
			if (queryCommond.getIsSuperBusinessAdmin())
				sql += " and ld.depname like :depname";
			else
				sql += " and d.depname like :depname";
			values.put("depname", "%" + queryCommond.getSearchDepName() + "%");
		}
		// 单位及职务查询
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUnit())) {
			sql += " and bu.duty_remark like :position";
			values.put("position", "%" + queryCommond.getSearchUnit() + "%");
		}
		// 审核状态查询
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchSignUpState())) {
			sql += " and c.auditing_State in (:auditingState)";
			values.put("auditingState", queryCommond.getSearchSignUpState());
		}

		// 查询民族是否为空
		if (PropertyUtil.objectNotEmpty(queryCommond.getIsNullByNation())) {
			sql += " and bt.data_name is null ";
		}

		sql += " and (bu.LOGIN_ID not like '9%') and bu.USER_NAME <> '通讯员' and (bu.USER_NAME not like '%管理员') ";

		// 增加排序
		if (queryCommond.getSortInfos() != null) {
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
				if (sortInfoBean.getProperty().trim().equals("trainingHour")) {
					sql += " order by \"trainingHour\" " + sortInfoBean.getDirection().trim();
				} else {
					sql += " order by " + sortInfoBean.getProperty().trim();
					if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
						sql += " " + sortInfoBean.getDirection().trim();
					}
				}
			}
		} else {
			sql += " order by d.CUSTOMFIELD6,bbb.order_num,bu.login_id";
		}
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder.setBaseQueryString(sql);
		defaultQueryConditionBuilder.setResultObject(QueryClassUserBean.class);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		// 查询总数量
		// Long count = this.count(queryCondition);
		if (queryCommond.getLimit() == -1) {
			return (List<QueryClassUserBean>) this.findList(queryCondition);
		}
		int count = this.executeFind(QueryStringType.SQL, sql, values).size();
		int firstResult = queryCommond.calculate(count);
		return (List<QueryClassUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
	}

	/**
	 * 按考勤状态查询班级人员列表
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author RongSL
	 * @createDate 2016-9-18
	 */
	@Override
	public List<QueryClassUserBean> findClassUserListByAttendanceState2(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> values = new HashMap<String, Object>();
		String sql = "select c.auditing_State as \"auditingState\",c.class_user_id as \"classUserId\",c.class_id as \"classId\",d.depid as \"deptId\",c.ATTENDANCE_STATE as \"attendanceState\",d.depname as \"depName\",nvl(tca.user_state,1) as \"userState\","
				+ " bu.userid as \"userId\",c.user_name as \"userName\",bu.login_id as \"loginID\",c.sign_up_type as \"signUpType\",bb.data_name as \"sex\",c.birthday as \"birthday\",c.duty_remark as \"position\",c.mobile_Telephone as \"mobileTelephone\",bt.data_name as \"folkName\",c.remark as \"remark\" "
				+ " from training_class_user c left join base_user bu on c.user_id = bu.userid left join training_class_attendance tca on bu.userid=tca.user_id LEFT JOIN base_BASEDATA bb ON c.sex_code = bb.DATA_CODE LEFT JOIN base_BASEDATA bt ON c.folk_Code = bt.DATA_CODE,"
				+ " base_department d, base_department_user du " + " where c.user_id = du.userid  and du.depid = d.depid and c.active_state=" + TrainingConstants.IS_ACTIVE_Y + " and bu.active_state=" + TrainingConstants.IS_ACTIVE_Y + " and d.active_state=" + TrainingConstants.IS_ACTIVE_Y
				+ " and du.active_state=" + TrainingConstants.IS_ACTIVE_Y + "";
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassID())) {
			sql += " and c.class_id = :classId";
			values.put("classId", queryCommond.getSearchClassID());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepId())) {
			sql += " and d.treepath like CONCAT((select dd.treepath from base_department dd where dd.depid = :depId), '%')";
			values.put("depId", queryCommond.getSearchDepId());
		}
		// if(PropertyUtil.objectNotEmpty(queryCommond.getAttendanceState())){
		// sql+=" and c.attendance_state = :attendanceState";
		// values.put("attendanceState", queryCommond.getAttendanceState());
		// }
		// 增加登陆号查询 by Max at 2015-4-3
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchLoginID())) {
			sql += " and bu.login_id = :loginId";
			values.put("loginId", queryCommond.getSearchLoginID());
		}
		// 增加姓名查询 by Max at 2015-4-3
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())) {
			sql += " and bu.user_name like :username";
			values.put("username", "%" + queryCommond.getSearchUserName() + "%");
		}
		// 增加单位名称查询 by Max at 2015-4-3
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepName())) {
			sql += " and d.depname like :depname";
			values.put("depname", "%" + queryCommond.getSearchDepName() + "%");
		}
		// 单位及职务查询
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUnit())) {
			sql += " and bu.duty_remark like :position";
			values.put("position", "%" + queryCommond.getSearchUnit() + "%");
		}
		// 审核状态查询
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchSignUpState())) {
			sql += " and c.auditing_State = :auditingState";
			values.put("auditingState", queryCommond.getSearchSignUpState());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserState())) {
			sql += " and nvl(tca.user_state,2)=:userState";
			values.put("userState", queryCommond.getSearchUserState());
		}
		sql += " and (bu.LOGIN_ID not like '9%') and bu.USER_NAME <> '通讯员' and (bu.USER_NAME not like '%管理员') ";
		// 增加排序
		if (queryCommond.getSortInfos() != null) {
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
				sql += " order by " + sortInfoBean.getProperty().trim();
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
					sql += " " + sortInfoBean.getDirection().trim();
				}
			}
		} else {
			// sql+=" order by d.depname,bu.login_id ";
			sql += " order by c.create_date desc";
		}
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder.setBaseQueryString(sql);
		defaultQueryConditionBuilder.setResultObject(QueryClassUserBean.class);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		// 查询总数量
		Long count = this.count(queryCondition);
		int firstResult = queryCommond.calculate(count.longValue());
		return (List<QueryClassUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
	}

	/**
	 * 按考勤状态查询班级人员列表
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author RongSL
	 * @createDate 2016-9-18
	 */
	@Override
	public List<QueryClassUserBean> findClassUserListYesSignUp(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> values = new HashMap<String, Object>();
		String sql = "select c.auditing_State as \"auditingState\",c.class_user_id as \"classUserId\",c.class_id as \"classId\",d.depid as \"deptId\",c.ATTENDANCE_STATE as \"attendanceState\",";
		if (queryCommond.getIsSuperBusinessAdmin())
			sql += "ld.depname";
		else
			sql += "d.depname";
		sql += " as \"depName\","
				// "+ nvl(tca.user_state,1) as \"userState\","
				+ " concat(bu.userid,'') as \"userId\",bbb.data_name \"rank\",bu.user_name as \"userName\"," + "bu.login_id as \"loginID\",c.sign_up_type as \"signUpType\",bb.data_name as \"sex\","
				+ "bu.birthday as \"birthday\",bu.duty_remark as \"position\",bu.mobile_Telephone as \"mobileTelephone\"," + "bt.data_name as \"folkName\",c.remark as \"remark\","
				+ "CASE WHEN tc.lock_state IS NULL OR tc.lock_state = 2 THEN '待审核' ELSE ( SELECT bb.data_name FROM base_basedata bb WHERE bb.data_code = c.examination_result ) END as \"examinationResult\", " + "ifnull(c.training_hours, 0) as \"trainingHour\" "
				+ "from training_class_user c left join training_class tc on tc.class_id = c.class_id left join " + SystemConstants.SYSTEM_CODE + "uum_user bu on c.user_id = bu.userid " +
				// "left join training_class_attendance tca on
				// bu.userid=tca.user_id " +
				"LEFT JOIN base_BASEDATA bb ON bu.sex_code = bb.DATA_CODE LEFT JOIN base_BASEDATA bbb ON bu.duty_level_code = bbb.DATA_CODE LEFT JOIN base_BASEDATA bt ON bu.folk_Code = bt.DATA_CODE"
				+ " left join base_department_user du on c.user_id = du.userid left join base_department d on du.depid = d.depid";
		if (queryCommond.getIsSuperBusinessAdmin())
			sql += " left join base_department ld on du.level_depid = ld.depid";
		sql += " where c.active_state=" + TrainingConstants.IS_ACTIVE_Y + " and bu.active_state=" + TrainingConstants.IS_ACTIVE_Y + " and d.active_state=" + TrainingConstants.IS_ACTIVE_Y + " and du.active_state=" + TrainingConstants.IS_ACTIVE_Y + "";
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassID())) {
			sql += " and c.class_id = :classId";
			values.put("classId", queryCommond.getSearchClassID());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepId())) {
			sql += " and d.treepath like CONCAT((select dd.treepath from base_department dd where dd.depid = :depId), '%')";
			values.put("depId", queryCommond.getSearchDepId());
		}
		// if(PropertyUtil.objectNotEmpty(queryCommond.getAttendanceState())){
		// sql+=" and c.attendance_state = :attendanceState";
		// values.put("attendanceState", queryCommond.getAttendanceState());
		// }
		// 增加登陆号查询 by Max at 2015-4-3
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchLoginID())) {
			sql += " and bu.login_id = :loginId";
			values.put("loginId", queryCommond.getSearchLoginID());
		}
		// 增加姓名查询 by Max at 2015-4-3
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())) {
			sql += " and bu.user_name like :username";
			values.put("username", "%" + queryCommond.getSearchUserName() + "%");
		}
		// 增加单位名称查询 by Max at 2015-4-3
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepName())) {
			if (queryCommond.getIsSuperBusinessAdmin())
				sql += " and ld.depname like :depname";
			else
				sql += " and d.depname like :depname";
			values.put("depname", "%" + queryCommond.getSearchDepName() + "%");
		}
		// 单位及职务查询
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUnit())) {
			sql += " and bu.duty_remark like :position";
			values.put("position", "%" + queryCommond.getSearchUnit() + "%");
		}
		// 审核状态查询
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchSignUpState())) {
			sql += " and c.auditing_State in (:auditingState)";
			values.put("auditingState", queryCommond.getSearchSignUpState());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserState())) {
			sql += " and nvl(tca.user_state,2)=:userState";
			values.put("userState", queryCommond.getSearchUserState());
		}
		// sql += " and (bu.LOGIN_ID not like '9%') and bu.USER_NAME <> '通讯员'
		// and (bu.USER_NAME not like '%管理员') ";
		// 增加排序
		if (queryCommond.getSortInfos() != null) {
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
				sql += " order by " + sortInfoBean.getProperty().trim();
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
					sql += " " + sortInfoBean.getDirection().trim();
				}
			}
		} else {
			// sql+=" order by d.depname,bu.login_id ";
			sql += "  order by d.customfield6,bbb.order_num,bu.login_id ";
		}
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder.setBaseQueryString(sql);
		defaultQueryConditionBuilder.setResultObject(QueryClassUserBean.class);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		String countSQL = null;
		int startIndex = -1;
		if ((startIndex = sql.toUpperCase().indexOf(" FROM T")) != -1)
			countSQL = sql.substring(startIndex);
		queryCondition.setQueryString(countSQL);
		// 查询总数量
		Long count = this.count(queryCondition);
		int firstResult = queryCommond.calculate(count.longValue());
		// int count = this.findList(queryCondition).size();
		// int firstResult = queryCommond.calculate(Long.valueOf(count));
		queryCondition.setQueryString(sql);
		return (List<QueryClassUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
	}

	/**
	 * 导出班级人员列表
	 * 
	 * @param queryCommond
	 * @return List<QueryClassUserBean>
	 * @throws Exception
	 * @author RongSL
	 * @createDate 2017-01-17
	 */
	@Override
	public List<QueryClassUserBean> findClassUserListYesSignUpPerfect(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> values = new HashMap<String, Object>();
		String sql = "select c.auditing_State as \"auditingState\",c.class_user_id as \"classUserId\",c.class_id as \"classId\",d.depid as \"deptId\",c.ATTENDANCE_STATE as \"attendanceState\",";
		if (queryCommond.getIsSuperBusinessAdmin())
			sql += "ld.depname";
		else
			sql += "d.depname";
		sql += " as \"depName\",CONVERT(ifnull(tca.user_state,1) ,signed) as \"userState\"," + " concat(bu.userid,'') as \"userId\",bbb.data_name \"rank\",bu.user_name as \"userName\","
				+ "bu.login_id as \"loginID\",c.sign_up_type as \"signUpType\",bb.data_name as \"sex\",bu.birthday as \"birthday\",bu.duty_remark as \"position\",bu.mobile_Telephone as \"mobileTelephone\"," + "bt.data_name as \"folkName\",c.remark as \"remark\", tc.class_name \"className\","
				+ "tc.class_num \"classNum\",  tc.sponsor_name \"sponsorName\",date_format(tc.begin_date,'%Y-%m-%d') \"beginDateStr\"," + "date_format(tc.end_date,'%Y-%m-%d') as \"endDateStr\", tc.CLASS_DESCRIPTION as \"classDescription\",tc.training_address as \"trainingAddress\", "
				+ "case when tc.lock_state is Null or tc.lock_state =2 then '待审核' else (SELECT bb.data_name FROM base_basedata bb WHERE bb.data_code = c.examination_result) end as \"examinationResult\","
				+ "ifnull(c.training_hours, 0) as \"trainingHours\", date_format(c.auditing_date,'%Y-%m-%d %H:%i:%s') as \"auditingDateStr\", date_format(c.create_date,'%Y-%m-%d %H:%i:%s') as \"createDateStr\" "
				+ " from training_class_user c left join training_class tc on tc.class_id = c.class_id left join " + SystemConstants.SYSTEM_CODE + "uum_user bu on c.user_id = bu.userid "
				+ "left join training_class_attendance tca on bu.userid=tca.user_id LEFT JOIN base_BASEDATA bb ON bu.sex_code = bb.DATA_CODE " + "LEFT JOIN base_BASEDATA bbb ON bu.duty_level_code = bbb.DATA_CODE LEFT JOIN base_BASEDATA bt ON bu.folk_Code = bt.DATA_CODE" + " left join "
				+ SystemConstants.SYSTEM_CODE + "uum_department_user du on c.user_id = du.userid left join "+SystemConstants.SYSTEM_CODE + "uum_department d on du.depid = d.depid";
		if (queryCommond.getIsSuperBusinessAdmin())
			sql += " left join base_department ld on du.level_depid = ld.depid";
		sql += " where 1 = 1 ";
		sql += "and c.active_state=" + TrainingConstants.IS_ACTIVE_Y + " and bu.active_state=" + TrainingConstants.IS_ACTIVE_Y + " and d.active_state=" + TrainingConstants.IS_ACTIVE_Y + " and du.active_state=" + TrainingConstants.IS_ACTIVE_Y + "";
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassID())) {
			sql += " and c.class_id = :classId";
			values.put("classId", queryCommond.getSearchClassID());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassIds())) {
			sql += " and c.class_id in (:classId)";
			values.put("classId", queryCommond.getSearchClassIds());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepId())) {
			sql += " and d.treepath like CONCAT((select dd.treepath from base_department dd where dd.depid = :depId), '%')";
			values.put("depId", queryCommond.getSearchDepId());
		}
		// if(PropertyUtil.objectNotEmpty(queryCommond.getAttendanceState())){
		// sql+=" and c.attendance_state = :attendanceState";
		// values.put("attendanceState", queryCommond.getAttendanceState());
		// }
		// 增加登陆号查询 by Max at 2015-4-3
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchLoginID())) {
			sql += " and bu.login_id = :loginId";
			values.put("loginId", queryCommond.getSearchLoginID());
		}
		// 增加姓名查询 by Max at 2015-4-3
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())) {
			sql += " and bu.user_name like :username";
			values.put("username", "%" + queryCommond.getSearchUserName() + "%");
		}
		// 增加单位名称查询 by Max at 2015-4-3
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepName())) {
			if (queryCommond.getIsSuperBusinessAdmin())
				sql += " and ld.depname like :depname";
			else
				sql += " and d.depname like :depname";
			values.put("depname", "%" + queryCommond.getSearchDepName() + "%");
		}
		// 单位及职务查询
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUnit())) {
			sql += " and bu.duty_remark like :position";
			values.put("position", "%" + queryCommond.getSearchUnit() + "%");
		}
		// 审核状态查询
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchSignUpState())) {
			sql += " and c.auditing_State in (:auditingState)";
			values.put("auditingState", queryCommond.getSearchSignUpState());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserState())) {
			sql += " and ifnull(tca.user_state,2)=:userState";
			values.put("userState", queryCommond.getSearchUserState());
		}
		// sql += " and (bu.LOGIN_ID not like '9%') and bu.USER_NAME <> '通讯员'
		// and (bu.USER_NAME not like '%管理员') ";
		// 增加排序
		if (PropertyUtil.objectNotEmpty(queryCommond.getClickSource()) && queryCommond.getClickSource().equals(ClassUser.SOURCE_AUDIT_TWO)) {
			if (queryCommond.getSortInfos() != null) {
				SortInfoBean sortInfoBean = queryCommond.getSortInfos();
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
					sql += " order by " + sortInfoBean.getProperty().trim();
					if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
						sql += " " + sortInfoBean.getDirection().trim();
					}
				}
			} else {
				// sql+=" order by d.depname,bu.login_id ";
				sql += "  order by tc.create_date desc,bbb.order_num,bu.login_id ";
			}
		} else {
			if (queryCommond.getSortInfos() != null) {
				SortInfoBean sortInfoBean = queryCommond.getSortInfos();
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
					sql += " order by " + sortInfoBean.getProperty().trim();
					if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
						sql += " " + sortInfoBean.getDirection().trim();
					}
				}
			} else {
				// sql+=" order by d.depname,bu.login_id ";
				sql += "  order by tc.create_date desc,d.customfield6,bbb.order_num,bu.login_id ";
			}

		}
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder.setBaseQueryString(sql);
		defaultQueryConditionBuilder.setResultObject(QueryClassUserBean.class);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		// 查询总数量
		return (List<QueryClassUserBean>) this.findList(queryCondition);
	}

	/**
	 * 按考勤状态查询班级人员人数
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author LongJZ
	 * @createDate 2015-3-26
	 */
	public Integer findClassUserNumByAttendanceState(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> values = new HashMap<String, Object>();
		String sql = "select count(c.class_user_id) " + " from training_class_user c left join base_user bu on c.user_id = bu.userid left join interior_user iu " + " on bu.login_id = iu.login_id, base_department d, base_department_user du " + " where c.user_id = du.userid and du.depid = d.depid "
				+ " and c.active_state = " + TrainingConstants.IS_ACTIVE_Y + " and c.auditing_state = " + ClassUser.IS_AUDITING_Y + " and bu.active_state = " + TrainingConstants.IS_ACTIVE_Y + " and d.active_state = " + TrainingConstants.IS_ACTIVE_Y + " and du.active_state = "
				+ TrainingConstants.IS_ACTIVE_Y;
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassID())) {
			sql += " and c.class_id = :classId";
			values.put("classId", queryCommond.getSearchClassID());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepId())) {
			sql += " and d.treepath like CONCAT((select dd.treepath from base_department dd where dd.depid = :depId), '%')";
			values.put("depId", queryCommond.getSearchDepId());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getAttendanceState())) {
			sql += " and c.attendance_state = :attendanceState";
			values.put("attendanceState", queryCommond.getAttendanceState());
		}
		List<BigDecimal> num = (List<BigDecimal>) this.executeFind(QueryStringType.SQL, sql.toString(), values);
		return num.get(0).intValue();
	}

	/**
	 * 更新审核状态
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author Ningss
	 * @createDate 2016-1-7
	 */
	@Override
	public void batchAuditingClassUser(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		String hql = "update ClassUser c set c.auditingState=:auditingState";
		if (PropertyUtil.objectNotEmpty(queryCommond.getRejectReason())) {
			hql += ", c.rejectReason = :rejectReason";
			params.put("rejectReason", queryCommond.getRejectReason());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getAuditingDate())) {
			hql += " ,c.auditingDate=:auditingDate ";
			params.put("auditingDate", new Date());
		}
		hql += " ,c.auditingUserId=:auditingUserId,c.auditingUserIP=:auditingUserIP,c.latestOperateDate = :latestOperateDate,c.lastOperateTime = :lastOperateTime ";
		params.put("auditingUserId", queryCommond.getAuditingUserId());
		params.put("auditingUserIP", queryCommond.getAuditingUserIP());
		params.put("latestOperateDate", new Date());
		params.put("lastOperateTime", new Date());
		hql += " where c.classUserId in(:classUserIds)";
		if (PropertyUtil.objectNotEmpty(queryCommond.getAuditingState())) {
			params.put("auditingState", queryCommond.getAuditingState());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getAuditingStateDateStr())) {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			String dstr = queryCommond.getAuditingStateDateStr();
			Date date = sdf.parse(dstr);
			params.put("auditingUserDate", date);
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassUserIds())) {
			params.put("classUserIds", queryCommond.getSearchClassUserIds());
		}
		this.executeUpdate(QueryStringType.HQL, hql, params);
	}

	/**
	 * 班级人员列表(委托部委办班在线报名系统)
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author NingSS
	 * @date 2016-1-7
	 */
	public List<QueryClassUserBean> findTrainingClassUserList(ClassUserQueryCommond queryCommond) throws Exception {
		List<QueryClassUserBean> findList = new ArrayList<QueryClassUserBean>();
		Map<String, Object> values = new HashMap<String, Object>();
		// 拼写查询的sql
		StringBuffer sql = new StringBuffer();
		sql.append("select cu.user_id as \"userId\",u.login_id as \"loginID\", u.user_name as \"userName\",bb.data_name as \"rank\", c.class_name as \"className\","
				+ "c.class_num as \"classNum\",d.depname as \"depName\",cu.auditing_state as \"auditingState\",c.ministrie_name as \"ministrieName\" from training_class_user cu " + "left join training_class c on cu.class_id = c.class_id, base_department_user du, base_department d,base_user u "
				+ " left join base_basedata bb  on u.duty_level_code = bb.data_code where cu.user_id = u.userid and du.userid = u.userid " + "and du.depid = d.depid and du.active_state=:activeState and d.active_state=:activeState  and u.active_state=:activeState ");
		values.put("activeState", TrainingClass.IS_ACTIVE_Y);
		// 查询条件
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassID())) {
			sql.append(" and cu.class_id = :classId");
			values.put("classId", queryCommond.getSearchClassID());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassIDs())) {
			sql.append(" and cu.class_id in (:classIds)");
			values.put("classIds", queryCommond.getSearchClassIDs());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())) {
			sql.append(" and u.user_name like :userName");
			values.put("userName", "%" + queryCommond.getSearchUserName().toString() + "%");
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepName())) {
			sql.append(" and d.depname like :depname");
			values.put("depname", "%" + queryCommond.getSearchDepName().toString() + "%");
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchRank())) {
			sql.append(" and bb.data_name like :dname");
			values.put("dname", "%" + queryCommond.getSearchRank().toString() + "%");
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchLoginID())) {
			sql.append(" and u.login_id = :loginId");
			values.put("loginId", queryCommond.getSearchLoginID());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getAuditingState())) {
			sql.append(" and cu.auditing_state = :auditingState");
			values.put("auditingState", queryCommond.getAuditingState());
		}
		sql.append(" order by cu.class_id ");
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(QueryClassUserBean.class);
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		// 查询总数量
		Long count = this.count(queryCondition);
		if (queryCommond.getPage() == -1) {
			findList = (List<QueryClassUserBean>) this.findList(queryCondition);
		} else {
			int firstResult = queryCommond.calculate(count.longValue());
			findList = (List<QueryClassUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
		}
		return findList;
	}

	/**
	 * 获取这部门下最大的用户编号
	 * 
	 * @throws Exception
	 * @author YangJC
	 * @createDate 2016-01-15
	 */
	@Override
	public String getUserNum(ClassUserQueryCommond vCommond) throws Exception {
		Map<String, Object> values = new HashMap<String, Object>();
		String hql = "select max(substring(cast(u.loginID as string),5,length(u.loginID))) as UserMaxNum from User u,DepartmentUser du,Department d where u.userId=du.user.userId and du.department.deptID=d.deptID and d.deptCode=:deptCode and u.loginID like:yy";
		values.put("deptCode", vCommond.getSearchDeptCode());
		values.put("yy", new SimpleDateFormat("yyyy").format(new Date()).substring(2, 4) + "%");
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(hql.toString());
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		List<Object> result = (List<Object>) this.findList(queryCondition);
		if (result.get(0) != null) {
			return result.get(0).toString();
		} else {
			return "empty";
		}
	}

	/**
	 * 根据人员Id更新班级人员关系对象
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author LongJZ
	 * @createDate 2016-1-21
	 */
	public void updateClassUserByUserId(User user, Integer auditingState, String classId) throws Exception {
		Map<String, Object> values = new HashMap<String, Object>();
		String hql = "update ClassUser cu set cu.userName = :userName, cu.sexCode = :sexCode,cu.dutyLevelCode=:dutyLevelCode, cu.birthday = :birthday, " + "cu.folkCode = :folkCode, cu.mobileTelephone = :mobileTelephone, cu.dutyRemark = :dutyRemark, cu.remark = :remark "
		// + "cu.auditingState = :auditingState "
				+ "where cu.user.userId = :userId and cu.trainingClass.classId=:classId";
		values.put("userId", user.getUserId());
		values.put("userName", user.getUserName());
		values.put("sexCode", user.getSexCode());
		values.put("birthday", user.getBirthday());
		values.put("folkCode", user.getFolkCode());
		values.put("dutyLevelCode", user.getDutyLevelCode());
		values.put("mobileTelephone", user.getMobileTelephone());
		values.put("dutyRemark", user.getDutyRemark());
		values.put("remark", user.getRemark());
		// values.put("auditingState", auditingState);
		values.put("classId", classId);
		this.executeUpdate(QueryStringType.HQL, hql, values);
	}

	/**
	 * 作废人员(撤销报名用)
	 * 
	 * @param userIds
	 * @return
	 * @throws Exception
	 * @author LongJZ
	 * @createDate 2016-1-22
	 */
	public void toVoidUserByUserId(String[] userIds) throws Exception {
		Map<String, Object> values = new HashMap<String, Object>();
		String hql = "update User u set u.userName = null, u.sexCode = null, u.birthday = null, " + "u.folkCode = null, u.mobileTelephone = null, u.dutyRemark = null, u.remark = null," + "u.operateTime = null, u.activeState = :activeState where u.userId in (:userIds)";
		values.put("userIds", userIds);
		values.put("activeState", User.IS_ACTIVE_N);
		this.executeUpdate(QueryStringType.HQL, hql, values);
	}

	/**
	 * 查询当前部门下登录号序号最小的活动人员(报名用)
	 * 
	 * @param userIds
	 * @return
	 * @throws Exception
	 * @author LongJZ
	 * @createDate 2016-1-22
	 */
	public User findMinLoginIdActiveUser(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> values = new HashMap<String, Object>();
		String sql = "select t.userid as \"userId\", t.login_id as \"loginID\" from base_user t where t.login_id = " + "(select min(u.login_id) from base_department_user du, base_user u" + " where du.userid = u.userid" + " and u.active_state = :activeState" + " and du.depid = :depId)";
		values.put("depId", queryCommond.getSearchDepId());
		values.put("activeState", User.IS_ACTIVE_N);
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(sql);
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultQueryConditionBuilder.setResultObject(User.class);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		List<User> result = (List<User>) this.findList(queryCondition);
		if (PropertyUtil.objectNotEmpty(result))
			return result.get(0);
		else
			return null;
	}

	/**
	 * 查询统一用户平台人员登录号(报名用)
	 * 
	 * @param queryCommond
	 * @return User
	 * @throws Exception
	 * @author RongSL
	 * @createDate 2016-12-24
	 */
	@Override
	public User findUserLoginIdUserId(ClassUserQueryCommond queryCommond) throws Exception {
		String sql = " select uum_login_id_seq.nextval@uum_link as loginid,UUM_USER_SEQ.NEXTVAL@UUM_LINK as userid  from dual ";
		List<?> result = this.executeFind(QueryStringType.SQL, sql, null);
		if (PropertyUtil.objectNotEmpty(result) && !result.isEmpty()) {
			User user = new User();
			Object o[] = (Object[]) result.get(0);
			user.setLoginID(o[0].toString());
			user.setUserId(o[1].toString());
			return user;
		} else
			return null;
	}

	/**
	 * 查询统一用户平台人员信息(报名用)
	 * 
	 * @param userId
	 * @return
	 * @throws Exception
	 * @author RongSL
	 * @createDate 2016-12-24
	 */
	public ClassUserQueryCommond findUserByUserId(ClassUserQueryCommond queryCommond) throws Exception {
		StringBuffer sb = new StringBuffer(" select  t.user_name        as \"userName\", to_char(t.sex_code)       as \"sexCode\", t.birthday         as \"birthdayDate\", "
				+ "t.duty_remark      as \"dutyRemark\", t.login_id         as \"searchLoginID\", to_char(t.duty_Level_Code)      \"rank\",  t.mobile_Telephone as \"mobileTelephone\", " + "to_char(t.folk_Code)       as \"folkCode\"  from uum_user@uum_link t  where 1=1  ");
		Map<String, Object> params = new HashMap<String, Object>();
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserID())) {
			sb.append(" and t.userid=:userId ");
			params.put("userId", queryCommond.getSearchUserID());
		}
		List<ClassUserQueryCommond> user = (List<ClassUserQueryCommond>) this.executeFind(QueryStringType.SQL, sb.toString(), params, ClassUserQueryCommond.class);
		if (PropertyUtil.objectNotEmpty(user) && PropertyUtil.objectNotEmpty(user.get(0))) {
			return user.get(0);
		}
		return null;
	}

	/**
	 * 根据人员ID查询审核状态
	 * 
	 * @param userId
	 * @return
	 * @throws Exception
	 * @author LongJZ
	 * @createDate 2016-1-26
	 */
	public Integer findAuditingStateByUserId(String userId) throws Exception {
		Map<String, Object> values = new HashMap<String, Object>();
		String sql = "select cu.auditing_state" + " from training_class_user cu" + " where cu.active_state = :activeState" + " and cu.user_id = :userId";
		values.put("userId", userId);
		values.put("activeState", ClassUser.IS_ACTIVE_STATE_Y);
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setBaseQueryString(sql);
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		List<BigDecimal> result = (List<BigDecimal>) this.findList(queryCondition);
		if (PropertyUtil.objectNotEmpty(result))
			return result.get(0).intValue();
		else
			return null;
	}

	/**
	 * 更新人员审核状态
	 * 
	 * @param queryCommond
	 * @return
	 * @throws Exception
	 * @author LongJZ
	 * @createDate 2016-1-26
	 */
	public void updateAuditingStateByUserId(ClassUserQueryCommond queryCommond) throws Exception {
		StringBuffer hql = new StringBuffer("update ClassUser c set c.auditingState = :auditingState ,"
				+ " c.latestOperateDate = :latestOperateDate," 
				+ " c.lastOperateTime = :lastOperateTime");
		hql.append(" where c.classUserId = :classUserId");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("auditingState", queryCommond.getAuditingState());
		params.put("latestOperateDate", new Date());
		params.put("lastOperateTime", new Date());
		params.put("classUserId", queryCommond.getSearchClassUserId());
		this.executeUpdate(QueryStringType.HQL, hql.toString(), params);
	}

	/**
	 * 驳回撤销报名
	 * 
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author LongJZ
	 * @createDate 2016-1-26
	 */
	public void rejectDeleteUser(ClassUserValidCommond commond) throws Exception {
		StringBuffer hql = new StringBuffer("update ClassUser c set c.auditingState = :auditingState," 
					+ " c.rejectReason = :rejectReason,c.latestOperateDate = :latestOperateDate,"
					+ " c.lastOperateTime = :lastOperateTime  where c.user.userId in (:userIds)");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("auditingState", ClassUser.IS_AUDITING_Y_CX_N);
		params.put("rejectReason", commond.getRejectReason());
		params.put("userIds", commond.getUserIds());
		params.put("latestOperateDate", new Date());
		params.put("lastOperateTime", new Date());
		this.executeUpdate(QueryStringType.HQL, hql.toString(), params);
	}

	/**
	 * 查看驳回原因
	 * 
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author LongJZ
	 * @createDate 2016-1-26
	 */
	public QueryClassUserBean findRejectReason(ClassUserValidCommond commond) throws Exception {
		Map<String, Object> values = new HashMap<String, Object>();
		String sql = "select cu.reject_reason as \"rejectReason\"" + " from training_class_user cu where cu.class_user_id = :classUserId";
		values.put("classUserId", commond.getClassUserId());
		List<QueryClassUserBean> result = (List<QueryClassUserBean>) this.executeFind(QueryStringType.SQL, sql, values, QueryClassUserBean.class);
		if (PropertyUtil.objectNotEmpty(result))
			return result.get(0);
		else
			return null;
	}

	/**
	 * 报名管理导出
	 * 
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author GUOLEI
	 * @createDate 2016-1-29
	 */
	public List<QueryClassUserBean> excelSelectedUserList(ClassUserQueryCommond queryCommond) throws Exception {

		Map<String, Object> values = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer();
		sql.append("select  " + "bu.login_id as \"loginID\" , " + // 登录号
				"bu.user_name as \"userName\" ," + // 人员姓名
				"sex.data_name as \"sex\", " + // 学员性别
				"bu.BIRTHDAY   as \"birthday\", " + // 学员生日
				"folk.data_name as \"folkName\"," + // 民族
				(queryCommond.getIsSuperBusinessAdmin() ? "ld.depname" : "bd.depname") + " as \"depName\"," + // 单位
				"bcu.DUTY_REMARK as \"dutyRemark\"," + // 职务
				"bbb.data_name as \"rank\"," + // 职级
				"tc.class_num as \"classNum\","
				// 专题班编号
				+ "tc.class_name as \"className\"," + // 专题班名称
				"tc.SPONSOR_NAME as \"sponsorID\"," + // 主办单位
				"tc.BEGIN_DATE as \"beginDate\"," + // 开班时间
				"tc.END_DATE as \"endDate\"," + // 结业时间
				"tc.class_description as \"trainingDeptName\"," + // 培训机构
				"tc.TRAINING_ADDRESS as \"trainingAddress\"," + // 培训地点
				/*
				 * "bu.mobile_telephone as \"phone\", " + // 电话 ""
				 */
				// + // 专题班编号

				/*
				 * "tc.MinisTRIE_NAME as \"ministrieName\"," + // 承办部委
				 */"tc.TRAINING_HOUR as \"trainingHours\" " + // 学时
				" from uum_user@uum_link bu left join training_class_user bcu on to_char(bu.userid) = bcu.user_id " + "left join training_class tc on tc.class_id = bcu.class_id " + "left join uum_department_user@uum_link bdu on bdu.userid = to_char(bu.userid) "
				+ "left join base_department bd on bd.depid = bdu.depid " + "left join base_department ld on ld.depid = bdu.level_depid " + "left join base_basedata sex on sex.data_code = to_char(bu.sex_code) " + "left join base_basedata folk on folk.data_code = to_char(bu.folk_code) "
				+ "left join base_basedata bbb on to_char(bu.duty_level_code) = bbb.data_code " + " where bcu.user_id = bdu.userid  and tc.class_id = bcu.class_id and tc.active_state=1 ");
		// 班级ID
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassIds())) {
			sql.append(" and tc.class_id in ( :classId ) ");
			values.put("classId", queryCommond.getSearchClassIds());
		}
		// 部委ID
		/*
		 * if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepId())) {
		 * sql.append(" and bd.depId = :depId "); values.put("depId",
		 * queryCommond.getSearchDepId()); }
		 */
		// 部委ID
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchMinistrieID())) {
			sql.append(" and tc.MinisTRIE_ID = :ministrieId ");
			values.put("ministrieId", queryCommond.getSearchMinistrieID());
		}
		// 报名通过
		if (PropertyUtil.objectNotEmpty(queryCommond.getAuditingState()) && queryCommond.getAuditingState() == ClassUser.IS_AUDITING_Y.intValue()) {
			sql.append(" and bcu.auditing_state in (:auditingState) ");
			values.put("auditingState", new Integer[] { ClassUser.IS_AUDITING_Y, ClassUser.IS_AUDITING_Y_CX_N });
		}
		// 报名人员在管理范围下
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchTreePath())) {
			sql.append(" and bd.treepath like :treePath ");
			values.put("treePath", queryCommond.getSearchTreePath() + "%");
		}
		// 单位，职级，学员LoginId
		sql.append(" order by bd.customfield6,bbb.order_num,bu.login_id ");

		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(QueryClassUserBean.class);
		defaultQueryConditionBuilder.setBaseQueryString(sql.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(values);
		List<QueryClassUserBean> result = (List<QueryClassUserBean>) this.findList(queryCondition);
		return result;
	}

	/**
	 * 学员评价
	 * 
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author RongSL
	 * @createDate 2016-2-23
	 */
	@Override
	public void userAppraise(ClassUserQueryCommond queryCommond) throws Exception {

		Map<String, Object> value = new HashMap<String, Object>();
		StringBuffer sb = new StringBuffer();
		sb.append("update training_class_user tcu set tcu.appraise=:appraise,tcu.appraise_Status=:aStatus  ");
		if (PropertyUtil.objectNotEmpty(queryCommond.getAppraise())) {
			value.put("appraise", queryCommond.getAppraise());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getAppraiseStatus())) {
			value.put("appraiseStatus", queryCommond.getAppraiseStatus());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassUserIds())) {
			sb.append("where  tcu.class_user_id in (:classUserId) ");
			value.put("classUserId", queryCommond.getSearchClassUserIds());
		}
		value.put("aStatus", ClassUserQueryCommond.APPRAISE_STATUS_YES);
		this.executeUpdate(QueryStringType.SQL, sb.toString(), value);
	}

	/**
	 * 学员评价
	 * 
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author RongSL
	 * @createDate 2016-2-23
	 */
	@Override
	public List<QueryClassUserBean> userAppraiseShow(ClassUserQueryCommond queryCommond) throws Exception {

		Map<String, Object> value = new HashMap<String, Object>();
		StringBuffer sb = new StringBuffer();
		sb.append("select tcu.class_user_id AS \"classUserId\"," + "bu.user_name \"userName\"," + "tc.class_name as \"className\"," + "tcu.appraise as \"appraise\"," + "bu.login_id as \"loginID\"," + "bd.data_name AS \"sex\"," + "bu.duty_remark \"remark\"," + "tc.class_id as  \"classId\","
				+ "bu.userid as \"userId\"," + "bu.birthday as \"birthday\"," + "bu.mobile_telephone as \"mobileTelephone\"," + "bd2.data_name as \"folkName\"," + "tcu.appraise_status as \"appraiseStatus\" " + "from training_class_user tcu " + "left join base_user bu on tcu.user_id=bu.userid "
				+ "left join base_department_user bdu on bdu.userid=bu.userid  " + "left join training_class tc on tc.class_id=tcu.class_id  " + "left join base_basedata bd on bu.sex_code=bd.data_code " + "left join base_basdata bd2 on bu.folk_code=bd2.data_code " + "where bdu.depid=:deptId ");
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepId())) {
			value.put("deptId", queryCommond.getSearchDepId());
		}
		sb.append(" order by tcu.create_date desc");
		IConditionBuilder defaultConditionBuilder = new DefaultQueryConditionBuilder();
		defaultConditionBuilder.setBaseQueryString(sb.toString());
		defaultConditionBuilder.setQueryStringType(QueryStringType.SQL);
		defaultConditionBuilder.setResultObject(QueryClassUserBean.class);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(value);
		List<QueryClassUserBean> result = null;
		// 查询总数量
		Long count = this.count(queryCondition);
		if (queryCommond.getPage() == -1) {
			result = (List<QueryClassUserBean>) this.findList(queryCondition);
		} else {
			int firstResult = queryCommond.calculate(count.longValue());
			result = (List<QueryClassUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
		}
		return result;
	}

	/**
	 * 解除学员锁定
	 * 
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author RongSL
	 * @createDate 2016-2-23
	 */
	@Override
	public void openLockUserAppraise(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> value = new HashMap<String, Object>();
		StringBuffer sb = new StringBuffer();
		sb.append("update training_class_user tcu set tcu.appraise_status =:appraiseStatus where tcu.class_user_id in(:classUserIds)");
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassUserIds())) {
			value.put("classUserIds", queryCommond.getSearchClassUserIds());
		}
		value.put("appraiseStatus", ClassUserQueryCommond.APPRAISE_STATUS_NO);
		this.executeUpdate(QueryStringType.SQL, sb.toString(), value);
	}

	/**
	 * 根据班级学员ID查询班级信息
	 * 
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author RongSL
	 * @createDate 2016-6-03
	 */
	@Override
	public List<?> findClassInfo(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> value = new HashMap<String, Object>();
		StringBuffer sb = new StringBuffer();
		sb.append("select tc.class_name,tc.ministrie_name,bd.depname,bu.user_name,tc.class_id " + "from training_class_user tcu " + "left join training_class tc on tcu.class_id=tc.class_id " + "left join base_user bu on tcu.user_id=bu.userid "
				+ "left join base_department_user bdu on bdu.userid=bu.userid " + "left join base_department bd on bd.depid=bdu.depid " + "where 1=1 ");
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassUserIds())) {
			sb.append(" and tcu.class_user_id in (:classUserIds) ");
			value.put("classUserIds", queryCommond.getSearchClassUserIds());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserIds())) {
			sb.append(" and bu.userid  in (:userids) ");
			value.put("userids", queryCommond.getSearchUserIds());
		}
		return this.executeFind(QueryStringType.SQL, sb.toString(), value);
	}

	/**
	 * 查看学员表现（单个学员）
	 * 
	 * @param commond
	 * @return
	 * @throws Exception
	 * @author RongSL
	 * @createDate 2016-2-23
	 */
	public List<QueryClassUserBean> userAppraiseShowOne(@ModelAttribute("result") @Valid ClassUserCommond queryCommond) throws Exception {
		Map<String, Object> value = new HashMap<String, Object>();
		StringBuffer sb = new StringBuffer();
		sb.append("select tcu.appraise as \"appraise\" from training_class_user tcu  where tcu.class_user_id=:classUserId");
		if (PropertyUtil.objectNotEmpty(queryCommond.getClassUserId())) {
			value.put("classUserId", queryCommond.getClassUserId());
		}
		IConditionBuilder defaultConditionBuilder = new DefaultQueryConditionBuilder();
		defaultConditionBuilder.setResultObject(QueryClassUserBean.class);
		defaultConditionBuilder.setBaseQueryString(sb.toString());
		defaultConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(value);
		List<QueryClassUserBean> result = (List<QueryClassUserBean>) this.findList(queryCondition);
		return result;
	}

	@Override
	public void save(IBaseEntity entity) {
		// TODO Auto-generated method stub
		super.save(entity);
		this.getSession().flush();
	}

	/**
	 * 已有用户更新考勤状态
	 * 
	 * @author RongSL
	 * @Date 2016-09-18
	 */
	@Override
	public void updateUserAttendanceState(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		String sql = "update  training_class_attendance tca set tca.user_state=:userState where tca.class_id=:classId and tca.user_id=:userId";
		params.put("userState", queryCommond.getSearchUserState());
		params.put("classId", queryCommond.getSearchClassID());
		params.put("userId", queryCommond.getSearchUserID());
		this.executeUpdate(QueryStringType.SQL, sql, params);
	}

	/**
	 * 更新考核状态
	 * 
	 * @author RongSL
	 * @Date 2017-02-23
	 */
	@Override
	public void updateExaminationState(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		String sql = "update  training_class tc set tc.lock_state=:lockState, latest_operate_date = :date where tc.class_id=:classId ";
		params.put("classId", queryCommond.getSearchClassID());
		params.put("lockState", queryCommond.getLockState());
		params.put("date", new Date());
		this.executeUpdate(QueryStringType.SQL, sql, params);
	}

	/**
	 * 新增用户考勤
	 * 
	 * @author RongSL
	 * @Date 2016-09-18
	 */
	@Override
	public void createUserAttendanceState(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		String sql = "insert into training_class_attendance tca values( :id,:classId,:userId,sysdate,:userState )";
		params.put("id", UUID.randomUUID().toString().replace("-", ""));
		params.put("userState", queryCommond.getSearchUserState());
		params.put("classId", queryCommond.getSearchClassID());
		params.put("userId", queryCommond.getSearchUserID());
		this.executeUpdate(QueryStringType.SQL, sql, params);
	}

	/**
	 * 获得用户是否已入考勤表
	 * 
	 * @author RongSL
	 * @Date 2016-09-18
	 */
	@Override
	public boolean getUserCount(ClassUserQueryCommond queryCommond) throws Exception {
		boolean flag = true;
		Map<String, Object> params = new HashMap<String, Object>();
		String sql = "select count(1) from training_class_attendance tca where tca.class_id =:classId and tca.user_id=:userId ";
		params.put("classId", queryCommond.getSearchClassID());
		params.put("userId", queryCommond.getSearchUserID());
		List<Integer> list = (List<Integer>) this.executeFind(QueryStringType.SQL, sql.toString(), params);
		if (PropertyUtil.objectNotEmpty(list) && PropertyUtil.objectNotEmpty(list.get(0))) {
			Integer a = Integer.valueOf(list.get(0) + "");
			if (a == 0) {
				flag = false;
			}
		} else {
			flag = false;
		}
		return flag;
	}

	/**
	 * 查询用户
	 * 
	 * @author RongSL
	 * @date 2017-01-09
	 */
	public User getUser(String userId) throws Exception {
		User u = this.find(User.class, userId);
		return u;
	}

	/**
	 * 过滤已报名人员，防止因为网络延迟误点
	 * 
	 * @author RongSl
	 * @Date 2017-1-11
	 */
	public List<Integer> filterUser(ClassUserQueryCommond queryCommond) throws Exception {
		String sql = " select tcu.user_id from training_class_user tcu where tcu.user_id in (:userIds) and tcu.class_id=:classId  ";
		Map<String, Object> params = new HashMap<String, Object>();
		List<Integer> userIds = new ArrayList<Integer>();
		params.put("userIds", queryCommond.getSearchUserIds());
		params.put("classId", queryCommond.getSearchClassID());
		List<?> list = this.executeFind(QueryStringType.SQL, sql, params);
		if (PropertyUtil.objectNotEmpty(list) && PropertyUtil.objectNotEmpty(list.get(0))) {
			Iterator<?> ite = list.iterator();
			while (ite.hasNext()) {
				Object o = ite.next();
				userIds.add(Integer.valueOf(o.toString()));
			}
		}
		return userIds;
	}

	/**
	 * 根据userid获得ClassUser
	 * 
	 * @Date 2017-1-12
	 * @author RongSL
	 */
	public Object getClassUserByUserId(ClassUserQueryCommond queryCommond) {
		String hql = " select tcu.auditing_state from training_class_user tcu where tcu.user_id=:userId ";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("userId", queryCommond.getSearchUserID());
		List<?> cu = (List<ClassUserValidCommond>) this.executeFind(QueryStringType.SQL, hql, params);
		if (PropertyUtil.objectNotEmpty(cu) && PropertyUtil.objectNotEmpty(cu.get(0))) {
			return cu.get(0);
		}
		return null;
	}

	/**
	 * 根据userid获得人员部门
	 * 
	 * @Date 2017-1-12
	 * @author RongSL
	 */
	public Object getUserDeptNameByUserId(UserInfoValidCommond userValidCommond) {
		String Hql = " select b.depname from " + SystemConstants.SYSTEM_CODE + "uum_department_user bd left join " + SystemConstants.SYSTEM_CODE + "uum_department b on bd.depid=b.depid where bd.userid=:userId";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("userId", userValidCommond.getUserId());
		List<?> du = (List<?>) this.executeFind(QueryStringType.SQL, Hql, params);
		if (PropertyUtil.objectNotEmpty(du) && PropertyUtil.objectNotEmpty(du.get(0))) {
			return du.get(0);
		}
		return null;
	}

	/**
	 * 根据userid获得人员一级部门
	 * 
	 * @Date 2017-1-15
	 * @author LongJZ
	 */
	public Object getUserLv1DeptNameByUserId(UserInfoValidCommond userValidCommond) {
		String Hql = " select b.depname from uum_department_user@uum_link bd left join base_department b on bd.level_depid=b.depid where bd.userid=:userId";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("userId", userValidCommond.getUserId());
		List<?> du = (List<?>) this.executeFind(QueryStringType.SQL, Hql, params);
		if (PropertyUtil.objectNotEmpty(du) && PropertyUtil.objectNotEmpty(du.get(0))) {
			return du.get(0);
		}
		return null;
	}

	/**
	 * 批量更新学员审核状态
	 * 
	 * @param queryCommond
	 * @throws Exception
	 * @author RongSL
	 * @date 2016年01月13日
	 */
	public void updateUserAuditState(ClassUserQueryCommond queryCommond) throws Exception {
		StringBuffer sql = new StringBuffer("");
		sql.append("update training_class_user tcu set tcu.auditing_State = :auditingState where tcu.CLASS_USER_ID in (:classUserIds)");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("auditingState", queryCommond.getAuditingState());
		params.put("classUserIds", queryCommond.getSearchClassUserIds());
		this.executeUpdate(QueryStringType.SQL, sql.toString(), params);
	}

	@Override
	public List<com.eorchis.module.trainingclass.querybean.TrainingClassQueryBean> findClassList(ClassUserQueryCommond queryCommond) {
		// TODO Auto-generated method stub
		// 拼写查询的sql
		StringBuffer hql = new StringBuffer();
		hql.append("select distinct t.classId as classId , t.createDate as createDate FROM TrainingClass t " + "left join t.classType ct left join t.applyWay aw " + "left join t.signUpDepartment sd,Department d  " + "where 1=1 and  d.deptID=t.sponsorID ");
		hql.append(" and t.publishState = 1 ");
		// and t.activeState =:activeState and sd.dept.deptID=:sponsorId
		Map<String, Object> values = new HashMap<String, Object>();

		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchActiveState())) {
			hql.append(" and t.activeState =:activeState ");
			values.put("activeState", queryCommond.getSearchActiveState());
		}

		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDepId())) {
			hql.append(" and sd.dept.deptID=:sponsorId ");
			values.put("sponsorId", queryCommond.getSearchDepId());
		}

		return (List<com.eorchis.module.trainingclass.querybean.TrainingClassQueryBean>) executeFind(QueryStringType.HQL, hql.toString(), values, com.eorchis.module.trainingclass.querybean.TrainingClassQueryBean.class);
	}

	/**
	 * 当用户所属部门与统一用户平台不同步时进行同步
	 * 
	 * @author RongSL
	 * @date 2017-01-15
	 */
	public void isSyncDepartment(String... string) {
		String sql = " update base_department_user bd set bd.depid= concat((  " + "select ud.depid from " + SystemConstants.SYSTEM_CODE + "uum_user u " + "left join " + SystemConstants.SYSTEM_CODE + "uum_department_user udu on udu.userid = u.userid " + "left join " + SystemConstants.SYSTEM_CODE
				+ "uum_department ud on udu.depid = ud.depid where u.userid =:userId  ),'') " + "where bd.depuser_id=concat(( select udu.depuser_id from " + SystemConstants.SYSTEM_CODE + "uum_user u " + "left join " + SystemConstants.SYSTEM_CODE + "uum_department_user udu on udu.userid=u.userid  "
				+ "left join " + SystemConstants.SYSTEM_CODE + "uum_department ud on udu.depid=ud.depid " + "where u.userid=:userId ),'') ";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("userId", string[0]);
		this.executeUpdate(QueryStringType.SQL, sql, params);
	}

	/**
	 * 判断所选人员在本地是否存在
	 */
	public boolean isFlagUser(String string) {
		String sql = " select * from base_user bu where bu.userid=:userId ";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("userId", string);
		if (this.executeFind(QueryStringType.SQL, sql, params).size() > 0) {
			return true;
		} else {
			return false;
		}
	}

	/**
	 * 判断所选人员在本地是否存在部门关联关系
	 */
	public boolean isFlagDepartmentUser(String string) {
		String sql = " select * from base_department_user bu where bu.userid=:userId ";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("userId", string);
		if (this.executeFind(QueryStringType.SQL, sql, params).size() > 0) {
			return true;
		} else {
			return false;
		}
	}

	/**
	 * 根据人员ID获取部门ID
	 */
	public String findDepartmentDepIdByUserId(String string) {
		String sql = " select depid from " + SystemConstants.SYSTEM_CODE + "uum_department_user where userid=" + string;
		List<?> list = this.executeFind(QueryStringType.SQL, sql, null);
		if (PropertyUtil.objectNotEmpty(list) && PropertyUtil.objectNotEmpty(list.get(0))) {
			return list.get(0).toString();
		}
		return null;
	}

	@Override
	public ClassUser getUser(String classId, String userId) {
		String sql = "select tcu.remark,tcu.mobile_Telephone from training_class_user tcu " + "left join base_user bu on bu.userid=tcu.user_id " + "where bu.userid='" + userId + "' and tcu.class_id='" + classId + "'";
		List<?> list = this.executeFind(QueryStringType.SQL, sql, null);
		if (PropertyUtil.objectNotEmpty(list) && PropertyUtil.objectNotEmpty(list.get(0))) {
			ClassUser classUser = new ClassUser();
			classUser.setRemark(PropertyUtil.objectNotEmpty(((Object[]) list.get(0))[0]) ? ((Object[]) list.get(0))[0].toString().toString() : "");
			classUser.setMobileTelephone(PropertyUtil.objectNotEmpty(((Object[]) list.get(0))[1]) ? ((Object[]) list.get(0))[1].toString().toString() : "");
			return classUser;
		}
		return null;
	}

	/**
	 * 更新人员信息
	 * 
	 * @date 2017-02-06
	 * @author RongSL
	 */
	@Override
	public void updateUser(QueryClassUserBean qc) {
		String sql = " update base_user bu set  ";
		Map<String, Object> params = new HashMap<String, Object>();
		if (PropertyUtil.objectNotEmpty(qc.getUserName())) {
			sql += " bu.user_name=:userName, ";
			params.put("userName", qc.getUserName());
		}
		if (PropertyUtil.objectNotEmpty(qc.getSexCode())) {
			sql += " bu.sex_code=:sexCode, ";
			params.put("sexCode", qc.getSexCode());
		}
		if (PropertyUtil.objectNotEmpty(qc.getRankCode())) {
			sql += " bu.duty_level_code=:rankCode, ";
			params.put("rankCode", qc.getRankCode());
		}
		if (PropertyUtil.objectNotEmpty(qc.getFolkCode())) {
			sql += " bu.folk_code=:folkCode, ";
			params.put("folkCode", qc.getFolkCode());
		}
		if (PropertyUtil.objectNotEmpty(qc.getPosition())) {
			sql += " bu.duty_remark=:position, ";
			params.put("position", qc.getPosition());
		}
		if (PropertyUtil.objectNotEmpty(qc.getBirthday())) {
			sql += " bu.birthday=:birthday ";
			params.put("birthday", qc.getBirthday());
		}
		sql += "where bu.userid=:userId ";
		params.put("userId", qc.getUserId());
		this.executeUpdate(QueryStringType.SQL, sql, params);
	}

	/**
	 * 新增匿名评估用户 （导出评估结果使用）
	 * 
	 * @Date 2017-02-06
	 * @author RongSL
	 */
	@Override
	public void addAnonymous(QueryClassUserBean qc) {
		String sql = " insert into training_class_user (class_user_id,class_id,active_state,create_date,user_id,auditing_state,user_name) values ( " + ":classUserId,:classId,:activeState,now(),:userId,:auditingState,:userName ) ";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("classUserId", UUID.randomUUID().toString().replace("-", ""));
		params.put("classId", qc.getClassId());
		params.put("activeState", ClassUser.IS_ACTIVE_STATE_N);
		params.put("userId", qc.getUserId());
		params.put("auditingState", ClassUser.IS_AUDITING_N_M);
		params.put("userName", qc.getUserName());
		this.executeUpdate(QueryStringType.SQL, sql, params);
	}

	/**
	 * 判断人员是否已经存在
	 * 
	 * @Date 2017-02-26
	 * @author RongSL
	 */
	public boolean existenceClassUser(QueryClassUserBean qc) {
		String sql = " select tcu.class_user_id from training_class_user tcu where tcu.class_id=:classId and tcu.user_id=:userId ";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("classId", qc.getClassId());
		params.put("userId", qc.getUserId());
		List<?> list = this.executeFind(QueryStringType.SQL, sql, params);
		if (PropertyUtil.objectNotEmpty(list) && PropertyUtil.objectNotEmpty(list.get(0)))
			return true;
		else
			return false;
	}

	/**
	 * 更细考核学时
	 * 
	 * @Date 2017-02-22
	 * @author RongSL
	 */
	@Override
	public void updateTrainingClassUserH(ClassUserQueryCommond queryCommond) {
		StringBuffer sb = new StringBuffer(" update training_class_user tcu ");
		Map<String, Object> params = new HashMap<String, Object>();
		if (PropertyUtil.objectNotEmpty(queryCommond.getTrainingHour())) {
			sb.append(" set tcu.training_hours=:hour ");
			params.put("hour", queryCommond.getTrainingHour());
		} else
			sb.append(" set tcu.training_hours='' ");
		sb.append(" where tcu.class_user_id=:classUserId ");
		params.put("classUserId", queryCommond.getSearchClassUserId());
		this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
	}

	/**
	 * 更细考核备注
	 * 
	 * @Date 2017-02-22
	 * @author RongSL
	 */
	@Override
	public void updateTrainingClassUserB(ClassUserQueryCommond queryCommond) {
		StringBuffer sb = new StringBuffer(" update training_class_user tcu ");
		Map<String, Object> params = new HashMap<String, Object>();
		if (PropertyUtil.objectNotEmpty(queryCommond.getExaminationRemark())) {
			sb.append(" set tcu.examination_remark=:remark ");
			params.put("remark", queryCommond.getExaminationRemark());
		} else {
			sb.append(" set tcu.examination_remark='' ");
		}
		sb.append(" where tcu.class_user_id=:classUserId ");
		params.put("classUserId", queryCommond.getSearchClassUserId());
		this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
	}

	/**
	 * 更细考核结果
	 * 
	 * @Date 2017-05-15
	 * @author RongSL
	 */
	@Override
	public void updateTrainingClassUserR(ClassUserQueryCommond queryCommond) {
		StringBuffer sb = new StringBuffer(" update training_class_user tcu ");
		Map<String, Object> params = new HashMap<String, Object>();
		if (PropertyUtil.objectNotEmpty(queryCommond.getExaminationResult())) {
			sb.append(" set tcu.examination_result=:result ");
			params.put("result", queryCommond.getExaminationResult());
		} else {
			sb.append(" set tcu.examination_result='1356' ");
		}
		sb.append(" where tcu.class_user_id=:classUserId ");
		params.put("classUserId", queryCommond.getSearchClassUserId());
		this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
	}

	/**
	 * 更细考核学时们
	 * 
	 * @Date 2017-09-25
	 * @author RongSL
	 */
	@Override
	public void updateTrainingClassUsersH(ClassUserQueryCommond queryCommond) {
		Map<String, Object> params = new HashMap<String, Object>();
		StringBuffer sb = new StringBuffer(" update training_class_user tcu set tcu.training_hours=(select tc.training_hour from training_class tc where class_id=tcu.class_id) where tcu.class_id=:classId and tcu.training_hours is null  ");
		params.put("classId", queryCommond.getSearchClassID());
		this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
	}

	/**
	 * 更细考核结果们
	 * 
	 * @Date 2017-09-25
	 * @author RongSL
	 */
	@Override
	public void updateTrainingClassUsersR(ClassUserQueryCommond queryCommond) {
		StringBuffer sb = new StringBuffer(" update training_class_user tcu set tcu.examination_result=1356 where tcu.class_id=:classId and tcu.examination_result is null  ");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("classId", queryCommond.getSearchClassID());
		this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
	}

	/**
	 * 报名通过情况
	 * 
	 * @author RongSL 2017年5月15日
	 */
	@Override
	public String signSituation(ClassUserQueryCommond queryCommond) throws Exception {
		StringBuffer sb = new StringBuffer("select concat(t.userCount, ',', (t.userCount - t.unqualified - t.notraining), ',', t.unqualified, ',', t.notraining ) " + "from (select count(1) as userCount, count(case c.examination_result when '1357' then 1 end) as unqualified, "
				+ "count(case c.examination_result when '1358' then 1 end) as notraining " + "from training_class_user c left join training_class tc on tc.class_id = c.class_id " + "left join base_user bu on c.user_id = bu.userid, base_department d "
				+ "left join base_department bd on d.parent_id = bd.depid, base_department_user du " + "left join base_department ld on du.level_depid = ld.depid where c.user_id = du.userid " + "and d.depid = du.depid and c.active_state = 1 and bu.active_state = 1 and du.active_state = 1 "
				+ "and du.active_state = 1 and c.class_id = :classId and c.auditing_State in (1, 5) and (bu.LOGIN_ID not like '9%') " + "and bu.USER_NAME <> '通讯员' and (bu.USER_NAME not like '%管理员')) t");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("classId", queryCommond.getSearchClassID());
		List<?> list = this.executeFind(QueryStringType.SQL, sb.toString(), params);
		if (PropertyUtil.objectNotEmpty(list) && PropertyUtil.objectNotEmpty(list.get(0))) {
			return list.get(0).toString();
		}
		return null;
	}

	/**
	 * 查询当前班级下的总人数，合格人数，市管总数
	 * 
	 * @author RongSL 2017年5月16日
	 */
	@Override
	public String findClassUserDetails(ClassUserQueryCommond queryCommond) throws Exception {
		StringBuffer sb = new StringBuffer(
				"select concat(t.userCount,',', (t.userCount - t.unqualified - t.notraining),',',t.shiguan) " + "from (select count(1) as userCount, count(case c.examination_result when '1357' then 1 end) as unqualified, " + "count(case c.examination_result when '1358' then 1 end) as notraining,"
						+ "count(case bu.shiguan when 1 then 1 end) as shiguan " + "from training_class_user c left join training_class tc on tc.class_id = c.class_id " + "left join " + SystemConstants.SYSTEM_CODE + " uum_user bu on c.user_id = bu.userid, base_department d "
						+ "left join base_department bd on d.parent_id = bd.depid, base_department_user du " + "left join base_department ld on du.level_depid = ld.depid where c.user_id = du.userid " + "and d.depid = du.depid and c.active_state = 1 and bu.active_state = 1 and du.active_state = 1 "
						+ "and du.active_state = 1 and c.class_id = :classId and c.auditing_State in (1, 5) and (bu.LOGIN_ID not like '9%') " + "and bu.USER_NAME <> '通讯员' and (bu.USER_NAME not like '%管理员')) t");
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("classId", queryCommond.getSearchClassID());
		List<?> list = this.executeFind(QueryStringType.SQL, sb.toString(), params);
		if (PropertyUtil.objectNotEmpty(list) && PropertyUtil.objectNotEmpty(list.get(0))) {
			return list.get(0).toString();
		}
		return null;
	}

	/**
	 * 推送班级人员数据至档案系统
	 * 
	 * @author RongSL 2017年5月16日
	 */
	public void insertDATrainingClass(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		StringBuffer sb = new StringBuffer();
		int i = 0;
		String[] bl = null;// 需要保留的数据集合
		String[] xz = null;// 需要新增的数据集合
		String[] gx = null;// 需要更新的数据集合
		String[] count = findClassUserDetails(queryCommond).split(",");
		params.put("classId", queryCommond.getSearchClassID());
		params.put("auditNum", count[1]);
		params.put("shiguanNum", count[2]);
		params.put("userNum", count[0]);
		sb = new StringBuffer("select count(1) from training_class tc where tc.class_id=:classId ");
		Object o = this.executeFind(QueryStringType.SQL, sb.toString(), params).get(0);
		if (!o.toString().equals("0")) {
			params.put("classState", 2);
		} else {
			params.put("classState", 1);
		}
		// 查询出需要保留的人，即档案系统自己添加的人
		sb = new StringBuffer("select t2.class_user_id from TRAINING_CLASS_user t2  " + "where t2.class_id=:classId and t2.class_user_id not in (select c.class_user_id " + "from training_class_user c left join training_class tc on tc.class_id = c.class_id "
				+ "left join " + SystemConstants.SYSTEM_CODE + " uum_user bu on c.user_id = bu.userid, base_department d " + "left join base_department bd on d.parent_id = bd.depid, base_department_user du " + "left join base_department ld on du.level_depid = ld.depid where c.user_id = du.userid "
				+ "and d.depid = du.depid and c.active_state = 1 and bu.active_state = 1 and du.active_state = 1 " + "and du.active_state = 1 and c.auditing_State in (1, 5) and (bu.LOGIN_ID not like '9%') "
				+ "and bu.USER_NAME <> '通讯员' and (bu.USER_NAME not like '%管理员') and c.class_id=t2.class_id )");
		List<String> oo = (List<String>) this.executeFind(QueryStringType.SQL, sb.toString(), params);
		if (PropertyUtil.objectNotEmpty(oo) && PropertyUtil.objectNotEmpty(oo.get(0))) {
			bl = new String[oo.size()];
			for (; i < bl.length; i++) {
				bl[i] = oo.get(i);
			}
			i = 0;
			params.put("bl", bl);
		}
		// 查询出新增的数据
		sb = new StringBuffer(" select c.class_user_id from training_class_user c " + "left join training_class tc on tc.class_id = c.class_id " + "left join uum_user@uum_link bu on c.user_id = bu.userid, base_department d "
				+ "left join base_department bd on d.parent_id = bd.depid, base_department_user du " + "left join base_department ld on du.level_depid = ld.depid where c.user_id = du.userid " + "and d.depid = du.depid and c.active_state = 1 and bu.active_state = 1 and du.active_state = 1 "
				+ "and du.active_state = 1 and c.auditing_State in (1, 5) and (bu.LOGIN_ID not like '9%') " + "and bu.USER_NAME <> '通讯员' and (bu.USER_NAME not like '%管理员') and c.class_id=:classId " + "and c.class_user_id not in ( select t2.class_user_id from TRAINING_CLASS_user@pxarchives_link t2 "
				+ "where t2.class_id=c.class_id ) ");
		oo = null;
		oo = (List<String>) this.executeFind(QueryStringType.SQL, sb.toString(), params);
		if (PropertyUtil.objectNotEmpty(oo) && PropertyUtil.objectNotEmpty(oo.get(0))) {
			xz = new String[oo.size()];
			for (; i < xz.length; i++) {
				xz[i] = oo.get(i);
			}
			i = 0;
			params.put("xz", xz);
		}
		// 查询出学时不一致的数据
		sb = new StringBuffer(" select t2.class_user_id from TRAINING_CLASS_user@pxarchives_link t2  " + "left join TRAINING_CLASS_user t on t2.class_user_id=t.class_user_id " + "left join training_class tc on t.class_id=tc.class_id "
				+ "where t2.class_id=:classId and nvl(t.training_hours,tc.training_hour)<>t2.training_hours ");
		oo = null;
		oo = (List<String>) this.executeFind(QueryStringType.SQL, sb.toString(), params);
		if (PropertyUtil.objectNotEmpty(oo) && PropertyUtil.objectNotEmpty(oo.get(0))) {
			gx = new String[oo.size()];
			for (; i < gx.length; i++) {
				gx[i] = oo.get(i);
			}
			i = 0;
			params.put("gx", gx);
		}

		// 先数据老清空
		sb = new StringBuffer("delete from CLASS_SCOPE_DEPARTMENT@pxarchives_link where CLASS_ID=:classId");
		this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
		// 当没有需要更新的人员时则不需要删除
		if (PropertyUtil.objectNotEmpty(gx)) {
			sb = new StringBuffer("delete from training_class_user@pxarchives_link where class_id=:classId");
			sb.append(" and class_user_id in (:gx)");
			this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
		}
		sb = new StringBuffer("delete from training_class@pxarchives_link where class_id=:classId");
		this.executeUpdate(QueryStringType.SQL, sb.toString(), params);

		// 插入班级
		sb = new StringBuffer(" insert into training_class@pxarchives_link (class_id,training_hour, class_name, classname_year, " + "class_num, class_type,begin_date, end_date, class_user_num, training_address, training_dept_id, training_dept_name, "
				+ "apply_way, sponsor_id, sponsor_name, class_description, active_state, create_date, latest_operate_date, fax, " + "contact_tel, contact_people, phone, lock_state, category_id, create_depname, beoff_station, audit_num, "
				+ "shiguan_num, user_num,state)  select class_id,training_hour,classname_year||'年'||class_name, classname_year||'年', class_num, class_type,begin_date, end_date, " + "class_user_num, training_address, training_dept_id, training_dept_name, apply_way, sponsor_id, sponsor_name, "
				+ "class_description, active_state, create_date, latest_operate_date, fax, contact_tel, contact_people, phone, " + "lock_state, category_id, sponsor_name,(select data_name from base_basedata bd where bd.data_code=beoff_station), "
				+ ":auditNum,:shiguanNum,:userNum,:classState from training_class where class_id=:classId ");
		this.executeUpdate(QueryStringType.SQL, sb.toString(), params);

		// 插入承办单位
		sb = new StringBuffer(" insert into CLASS_SCOPE_DEPARTMENT@pxarchives_link select * from CLASS_SCOPE_DEPARTMENT where CLASS_ID=:classId ");
		this.executeUpdate(QueryStringType.SQL, sb.toString(), params);

		// 插入班级人员
		sb = new StringBuffer(" insert into training_class_user@pxarchives_link c ( c.class_user_id, c.user_id, c.class_id, c.auditing_date, " + "c.auditing_state, c.training_hours, c.user_name, c.sex_code, c.birthday, c.folk_code, c.duty_remark, c.duty_level_code, "
				+ "c.shiguan,c.mobile_Telephone, c.lock_state, c.active_state, c.gb_state,c.reject_Reason,state) select t.class_user_id, t.user_id, t.class_id, t.auditing_date, "
				+ "decode(t.examination_result,null,1,'1356',1,'1357',2,'1358',3),nvl(t.training_hours,tc.training_hour), t.user_name, t.sex_code, t.birthday, t.folk_code, t.duty_remark, t.duty_level_code, "
				+ "u.shiguan,t.mobile_Telephone, 1, 1, 0,t.examination_Remark,:userState from training_class_user t left join training_class tc on t.class_id=tc.class_id left join uum_user@uum_link u on t.user_id = to_char(u.userid) where t.class_id = :classId "
				+ "and t.class_user_id in (:classUserId"
				// + " select c.class_user_id from training_class_user c left
				// join training_class tc on tc.class_id = c.class_id "
				// + "left join uum_user@uum_link bu on c.user_id = bu.userid,
				// base_department d left join base_department bd on d.parent_id
				// = bd.depid, "
				// + "base_department_user du left join base_department ld on
				// du.level_depid = ld.depid where c.user_id = du.userid "
				// + "and d.depid = du.depid and c.active_state = 1 and
				// bu.active_state = 1 and du.active_state = 1 and
				// du.active_state = 1 "
				// + "and c.class_id = t.class_id and c.auditing_State in (1, 5)
				// and (bu.LOGIN_ID not like '9%') and bu.USER_NAME <> '通讯员' and
				// (bu.USER_NAME not like '%管理员')"
				+ ")");
		if (PropertyUtil.objectNotEmpty(xz)) {
			params.put("userState", 1);// 新增数据
			params.put("classUserId", xz);
			this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
		}
		if (PropertyUtil.objectNotEmpty(gx)) {
			params.put("userState", 2);// 更新数据
			params.put("classUserId", gx);
			this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
		}
		if (PropertyUtil.objectNotEmpty(bl)) {
			params.put("userState", 3);// 待删除 数据
			params.put("classUserId", bl);
			sb = new StringBuffer(" update training_class_user@pxarchives_link set state=:userState where class_id=:classId and class_user_id  in (:classUserId) ");
			this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
		}
	}

	/**
	 * 过滤掉不存的帐号和已报名的帐号
	 * 
	 * @author RongSL 2017年9月1日
	 */
	public List<?> filterSignUpUser(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		String sql = " select bu.login_id,bu.userid from base_user bu where bu.login_id in (:loginIds) " + "and bu.userid not in (select tcu.user_id from training_class_user tcu where tcu.class_id=:classId) ";
		params.put("loginIds", queryCommond.getSearchLoginIds());
		params.put("classId", queryCommond.getSearchClassID());
		List<?> list = this.executeFind(QueryStringType.SQL, sql, params);
		return list;
	}

	/**
	 * 获取统一用户平台部门人员临时关联表序列
	 * 
	 * @Date 2017-9-9
	 * @author RongSL
	 */
	public Object findTempDepUserId() {
		String sql = " select UUM_TEMP_DEPARTMENT_USER_SEQ.nextval from dual ";
		List<?> list = this.executeFind(QueryStringType.SQL, sql, null);
		if (PropertyUtil.objectNotEmpty(list) && PropertyUtil.objectNotEmpty(list.get(0)))
			return list.get(0);
		else
			return null;
	}

	/**
	 * 获取统一用户平台部门人员关联表序列
	 * 
	 * @Date 2017-9-9
	 * @author RongSL
	 */
	public Object findDepUserId() {
		String sql = " select UUM_DEPARTMENT_USER_SEQ.nextval from dual ";
		List<?> list = this.executeFind(QueryStringType.SQL, sql, null);
		if (PropertyUtil.objectNotEmpty(list) && PropertyUtil.objectNotEmpty(list.get(0)))
			return list.get(0);
		else
			return null;
	}

	/**
	 * @author RongSL 2017年9月9日 添加用户&部门关联
	 */
	@Override
	public boolean addTempUser(User u, Department d) {
		boolean flag = true;
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("activeState", UserBean.ACTIVE_ENABLED);
		// 添加管理员
		try {
			StringBuffer sb1 = new StringBuffer(" insert into UUM_TEMP_USER@uum_link (LOGIN_ID, PASSWORD, USER_NAME, " + "CURRENT_DUTY_TIME, SEX_CODE, NATIVE_PLACE_CODE, BIRTHDAY, PHONE, MOBILE_TELEPHONE, " + "EMAIL, USER_CODE, SPELL_CODE, QUESTION, ANSWER, LAST_LOGIN_DATE, CREATE_DATE, FOLK_CODE, "
					+ "PAPER_TYPE_CODE, PAPER_NUM, POLITICES_CODE, DEGREE_CODE, DUTY_LEVEL_CODE, DUTY_CODE, " + "DUTY_REMARK, ZUGONG, EXAM_STATE, CHISEL_STATE, ADMIN_STATE, STUDENT_STATE, TEACHER_STATE, "
					+ "AVAILABILITY_STATE, VIEW_STATE, ACTIVE_STATE, UPDATE_STATE, HIERARCHY, IS_BUREAU_RESERVE, ORDER_NUM, " + "OFFICEHOLDING_DATE, DANGWU, TONGZHAN, SEQ_NUM, LEVEL_CODE, STATUS_CODE, SHIGUAN, INITIALED_DATE, "
					+ "USERID) values (:loginId, :passWord, :userName, null, :sexCode, null, :birthDay, null, :phone, null, null, null, null, null, null, sysdate, " + ":folkCode, '1222', null, '1175', '-200', :dutyLevelCode, null, :dutyRemark, 0, 1, 0, 0, "
					+ "1, 0, 0, 1, 1, 0, 0, 0, 0, null, 0, 0, 0, null, null, 0, null, :userId)");
			params.put("loginId", u.getLoginID());
			params.put("userName", u.getName());
			params.put("sexCode", u.getSexCode().getDataCode());
			params.put("birthDay", u.getBirthday());
			params.put("phone", u.getMobileTelephone());
			params.put("folkCode", u.getFolkCode().getDataCode());
			params.put("dutyLevelCode", u.getDutyLevelCode().getDataCode());
			params.put("dutyRemark", u.getDutyRemark());
			params.put("userId", u.getUserId());
			params.put("passWord", UserBean.defaultPassWord);
			int i = this.executeUpdate(QueryStringType.SQL, sb1.toString(), params);
			if (i < 1) {
				flag = false;
			}
		} catch (Exception e) {
			flag = false;
			System.out.println("添加临时学员出错！");
			e.printStackTrace();
		}
		// 初始化所在部门
		try {
			StringBuffer sb2 = new StringBuffer(
					" insert into UUM_TEMP_DEPARTMENT_USER@uum_link (ORDER_NUM, ACTIVE_STATE, " + "UPDATE_STATE, LAST_OPERATE_TIME, DEPID, OLD_DEPTID, USERID, DEPUSER_ID) values " + "(null, 1, 0, sysdate, :depId,null, :userId, UUM_TEMP_DEPARTMENT_USER_SEQ.nextval@uum_link)  ");
			params.put("depId", d.getDeptID());
			int i = this.executeUpdate(QueryStringType.SQL, sb2.toString(), params);
			if (i < 1) {
				flag = false;
			}
		} catch (Exception e) {
			flag = false;
			System.out.println("建立临时部门学员关联出错！");
			e.printStackTrace();
		}
		return flag;
	}

	/**
	 * @author RongSL 2017年9月9日 添加用户&部门关联
	 */
	@Override
	public boolean addUser(User u, Department d) {
		boolean flag = true;
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("activeState", UserBean.ACTIVE_ENABLED);
		// 添加管理员
		try {
			StringBuffer sb1 = new StringBuffer(" insert into UUM_USER@uum_link (LOGIN_ID, PASSWORD, USER_NAME, " + "CURRENT_DUTY_TIME, SEX_CODE, NATIVE_PLACE_CODE, BIRTHDAY, PHONE, MOBILE_TELEPHONE, " + "EMAIL, USER_CODE, SPELL_CODE, QUESTION, ANSWER, LAST_LOGIN_DATE, CREATE_DATE, FOLK_CODE, "
					+ "PAPER_TYPE_CODE, PAPER_NUM, POLITICES_CODE, DEGREE_CODE, DUTY_LEVEL_CODE, DUTY_CODE, " + "DUTY_REMARK, ZUGONG, EXAM_STATE, CHISEL_STATE, ADMIN_STATE, STUDENT_STATE, TEACHER_STATE, " + "AVAILABILITY_STATE, VIEW_STATE, ACTIVE_STATE, HIERARCHY, IS_BUREAU_RESERVE, ORDER_NUM, "
					+ "OFFICEHOLDING_DATE, DANGWU, TONGZHAN, LEVEL_CODE, STATUS_CODE, SHIGUAN, INITIALED_DATE, " + "USERID,last_operate_time) values (:loginId, :passWord, :userName, null, :sexCode, null, :birthDay, null, :phone, null, null, null, null, null, null, sysdate, "
					+ ":folkCode, '1222', null, '1175', '-200', :dutyLevelCode, null, :dutyRemark, 0, 1, 0, 0, " + "1, 0, 0, 1, 1, 0, 0, 0, null, 0, 0, null, null, 0, null, :userId, sysdate)");
			params.put("loginId", u.getLoginID());
			params.put("userName", u.getName());
			params.put("sexCode", u.getSexCode().getDataCode());
			params.put("birthDay", u.getBirthday());
			params.put("phone", u.getMobileTelephone());
			params.put("folkCode", u.getFolkCode().getDataCode());
			params.put("dutyLevelCode", u.getDutyLevelCode().getDataCode());
			params.put("dutyRemark", u.getDutyRemark());
			params.put("userId", u.getUserId());
			params.put("passWord", UserBean.defaultPassWord);
			int i = this.executeUpdate(QueryStringType.SQL, sb1.toString(), params);
			if (i < 1) {
				flag = false;
			}
		} catch (Exception e) {
			flag = false;
			System.out.println("添加学员出错！");
			e.printStackTrace();
		}
		// 初始化所在部门
		try {
			StringBuffer sb2 = new StringBuffer(
					" insert into UUM_DEPARTMENT_USER@uum_link (ORDER_NUM, ACTIVE_STATE, " + "LAST_OPERATE_TIME,LAST_OPERATE_TYPE, DEPID, OLD_DEPTID, USERID, DEPUSER_ID,LEVEL_DEPID) values " + "(null, 1, sysdate,1, :depId,null, :userId, UUM_DEPARTMENT_USER_SEQ.nextval@uum_link,:levelDepId)  ");
			params.put("depId", d.getDeptID());
			params.put("levelDepId", d.getDeptID());
			// params.put("levelDepId", d.getParentID());
			int i = this.executeUpdate(QueryStringType.SQL, sb2.toString(), params);
			if (i < 1) {
				flag = false;
			}
		} catch (Exception e) {
			flag = false;
			System.out.println("建立部门学员关联出错！");
			e.printStackTrace();
		}
		return flag;
	}

	/**
	 * @author RongSL 2017年9月15日 是否是市管单位或局级
	 */
	@Override
	public boolean isShiguanOrJj(String userId) {
		boolean flag = false;
		Map<String, Object> params = new HashMap<String, Object>();
		try {
			StringBuffer sb1 = new StringBuffer(" select count(1) from " + SystemConstants.SYSTEM_CODE + "uum_user bu where bu.userid=:userId and bu.shiguan=0 and bu.duty_level_code not in (1380,1381)");
			params.put("userId", userId);
			List<Object> list = (List<Object>) this.executeFind(QueryStringType.SQL, sb1.toString(), params);
			if (PropertyUtil.objectNotEmpty(list) && PropertyUtil.objectNotEmpty(list.get(0)) && !list.get(0).toString().equals("1")) {
				flag = false;
			} else {
				flag = true;
			}
		} catch (Exception e) {
			flag = false;
			e.printStackTrace();
		}
		return flag;
	}

	/**
	 * @author RongSL 2017年9月15日 更新临时用户
	 */
	@Override
	public void updateTempUser(User u) {
		Map<String, Object> params = new HashMap<String, Object>();
		try {
			StringBuffer sb1 = new StringBuffer(" update " + SystemConstants.SYSTEM_CODE + "UUM_TEMP_USER set "
			// + "USER_NAME=:userName,"
					+ "SEX_CODE=:sexCode," + "DUTY_LEVEL_CODE=:dutyLevelCode,BIRTHDAY=:birthday,FOLK_CODE=:folkCode," + "MOBILE_TELEPHONE=:mobileTelephone,DUTY_REMARK=:dutyRemark where USERID=:userId");
			// params.put("userName", u.getName());
			params.put("sexCode", u.getSexCode().getDataCode());
			params.put("birthday", u.getBirthday());
			params.put("mobileTelephone", u.getMobileTelephone());
			params.put("folkCode", u.getFolkCode().getDataCode());
			params.put("dutyLevelCode", u.getDutyLevelCode().getDataCode());
			params.put("dutyRemark", u.getDutyRemark());
			params.put("userId", u.getUserId());
			this.executeUpdate(QueryStringType.SQL, sb1.toString(), params);
		} catch (Exception e) {
			System.out.println("添加学员出错！");
			e.printStackTrace();
		}
	}

	/**
	 * @author RongSL 2017年9月15日 更新用户
	 */
	@Override
	public void updateUser(User u) {
		Map<String, Object> params = new HashMap<String, Object>();
		try {
			StringBuffer sb1 = new StringBuffer(" update " + SystemConstants.SYSTEM_CODE + "UUM_USER set "
			// + "USER_NAME=:userName,"
					+ "SEX_CODE=:sexCode," + "DUTY_LEVEL_CODE=:dutyLevelCode,BIRTHDAY=:birthday,FOLK_CODE=:folkCode," + "MOBILE_TELEPHONE=:mobileTelephone,DUTY_REMARK=:dutyRemark,last_operate_time=now() where USERID=:userId");
			// params.put("userName", u.getName());
			params.put("sexCode", u.getSexCode().getDataCode());
			params.put("birthday", u.getBirthday());
			params.put("mobileTelephone", u.getMobileTelephone());
			params.put("folkCode", u.getFolkCode().getDataCode());
			params.put("dutyLevelCode", u.getDutyLevelCode().getDataCode());
			params.put("dutyRemark", u.getDutyRemark());
			params.put("userId", u.getUserId());
			this.executeUpdate(QueryStringType.SQL, sb1.toString(), params);
		} catch (Exception e) {
			System.out.println("添加学员出错！");
			e.printStackTrace();
		}
	}

	/**
	 * @author RongSL 2017年9月9日 记录审计日志
	 */
	@Override
	public boolean auditUserLogAdd(String... params) {
		Session session = this.getSession();
		// Transaction ts = null;
		try {
			// ts = session.beginTransaction();
			Connection conn = session.connection();
			String pro = "{call ADD_USER_LOG_PRO('" + params[0] + "','" + params[1] + "','" + params[2] + "')}";
			CallableStatement call = conn.prepareCall(pro);
			call.execute();
			// ts.commit();
		} catch (Exception e) {
			// ts.rollback();
			e.printStackTrace();
		} finally {
			session.clear();
			// session.close();
		}
		return true;
	}

	/**
	 * @author RongSL 2017年9月9日 记录审计日志
	 */
	@Override
	public boolean auditUserLogUpdate(QueryClassUserBean user, String... params) {
		Session session = this.getSession();
		// Transaction ts = null;
		try {
			// ts = session.beginTransaction();
			Connection conn = session.connection();
			String pro = "{call UPDATE_USER_LOG_PRO("
					// + "'" + user.getUserName()
					// + "',"
					+ "'" + user.getSex() + "','" + user.getRank() + "','" + new SimpleDateFormat("yyyy-MM-dd").format(user.getBirthday()) + "','" + user.getFolkName() + "','" + user.getMobileTelephone() + "','" + user.getDutyRemark() + "'," + params[0] + "," + user.getUserId() + ",'" + params[1]
					+ "')}";
			CallableStatement call = conn.prepareCall(pro);
			call.execute();
			// ts.commit();
		} catch (Exception e) {
			// ts.rollback();
			e.printStackTrace();
		} finally {
			session.clear();
			// session.close();
		}
		return true;
	}

	/**
	 * 从统一用户平台获得人员信息
	 * 
	 * @author RongSL 2017年10月10日
	 */
	@Override
	public QueryClassUserBean getUserbyUnified(String userId) throws Exception {
		String sql = " select uu.user_name \"userName\",ub.data_name \"sex\",uu.birthday \"birthday\",ub3.data_name \"rank\",ub2.data_name " + "\"folkName\",ifnull(uu.mobile_telephone,'无数据') \"mobileTelephone\",uu.duty_remark \"dutyRemark\",concat(uu.userid,'') \"userId\","
				+ "concat(uu.sex_code,'') \"sexCode\",concat(uu.duty_level_code,'') \"rankCode\",concat(uu.folk_code,'') \"folkCode\" " + "from " + SystemConstants.SYSTEM_CODE + "uum_user uu left join  " + SystemConstants.SYSTEM_CODE + "uum_basedata ub on ub.data_id=uu.sex_code " + "left join  "
				+ SystemConstants.SYSTEM_CODE + "uum_basedata ub2 on ub2.data_id=uu.folk_code " + "left join  " + SystemConstants.SYSTEM_CODE + "uum_basedata ub3 on ub3.data_id=uu.duty_level_code where uu.userid='" + userId + "'";
		List<QueryClassUserBean> list = (List<QueryClassUserBean>) this.executeFind(QueryStringType.SQL, sql, null, QueryClassUserBean.class);
		return list.get(0);
	}

	@Override
	public List<QueryClassUserBean> findLearningExperienceList(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		StringBuffer sb = new StringBuffer();
		sb.append(" select tcu.class_user_id \"classUserId\",tc.class_name \"className\",tc.sponsor_name \"sponsorName\",bb.data_name \"classType\",tc.begin_date \"beginDate\","
				+ "tc.end_Date \"endDate\",tcu.study_id \"attachmentID\",ba.primeval_file_name \"fileName\",to_char(ba.file_size) \"fileSize\" " + "from training_class_user tcu left join training_class tc on tcu.class_id=tc.class_id " + "left join base_basedata bb on tc.class_type=bb.data_code "
				+ "left join base_attachment ba on tcu.study_id=ba.attachment_id " + "where tcu.user_id=:userId ");
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassName())) {
			sb.append(" and tc.class_name like :className ");
			params.put("className", "%" + queryCommond.getSearchClassName() + "%");
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getUploadStudyState())) {
			if (queryCommond.getUploadStudyState().equals(ClassUserQueryCommond.UPLOAD_STATUS_YES))
				sb.append(" nd tcu.study_id is not null ");
			else if (queryCommond.getUploadStudyState().equals(ClassUserQueryCommond.UPLOAD_STATUS_NO))
				sb.append(" nd tcu.study_id is null ");
		}
		sb.append(" order by tc.begin_date desc,tc.end_date");
		params.put("userId", queryCommond.getSearchUserID());

		List<QueryClassUserBean> findList;
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(QueryClassUserBean.class);
		defaultQueryConditionBuilder.setBaseQueryString(sb.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		// 查询总数量
		Long count = this.count(queryCondition);
		if (queryCommond.getPage() == -1) {
			findList = (List<QueryClassUserBean>) this.findList(queryCondition);
		} else {
			int firstResult = queryCommond.calculate(count.longValue());
			findList = (List<QueryClassUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
		}
		return findList;
	}

	@Override
	public void updateUserAttachment(String... strings) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		String sql = "update training_class_user tcu set tcu.study_id=:attachmentID,upload_date=now() where tcu.class_user_id=:classUserId";
		params.put("attachmentID", strings[0]);
		params.put("classUserId", strings[1]);
		this.executeUpdate(QueryStringType.SQL, sql, params);
	}

	@Override
	public void settingUserAttachments(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		String sql = "update training_class_user tcu set tcu.study_id='',upload_date=now() where tcu.class_user_id in (:classUserIds)";
		params.put("classUserIds", queryCommond.getClassUserIds());
		this.executeUpdate(QueryStringType.SQL, sql, params);
	}

	/**
	 * @author RongSL 2017年9月22日 获得附件Blob
	 */
	@Override
	public Blob getAttachmentContent(AttachmentCommond attachmentCommond) {
		String sql = "select t.content from Base_Attachment t where t.attachment_id = ? ";
		String attachmentId = attachmentCommond.getAttachmentID();
		Connection con = this.getSession().connection();
		PreparedStatement prepareStatement = null;
		try {
			prepareStatement = con.prepareStatement(sql);
			prepareStatement.setString(1, attachmentId);
			ResultSet resultSet = prepareStatement.executeQuery();
			if (resultSet.next()) {
				Blob blob = resultSet.getBlob("CONTENT");
				return blob;
			} else
				return null;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				prepareStatement.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	/**
	 * @author RongSL 2017年9月27日
	 */
	@Override
	public void updateJJUserByUnified(String[] userIds) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			params.put("userIds", userIds);
			String sql = " update base_user bu set (bu.user_name,bu.sex_code,bu.duty_level_code,bu.folk_code,bu.duty_remark,bu.birthday)=(select u.user_name,u.sex_code,u.duty_level_code,u.folk_code,u.duty_remark,u.birthday from uum_user@uum_link u where bu.userid=u.userid) where to_char(bu.userid) in (select to_char(uu.userid) from uum_user@uum_link uu where uu.duty_level_code in (1380,1381) and uu.userid in (:userIds)) ";
			this.executeUpdate(QueryStringType.SQL, sql, params);
			sql = " update training_class_user tcu set (tcu.user_name,tcu.sex_code,tcu.duty_level_code,tcu.folk_code,tcu.duty_remark,tcu.birthday)=(select u.user_name,u.sex_code,u.duty_level_code,u.folk_code,u.duty_remark,u.birthday from uum_user@uum_link u where tcu.user_id=u.userid) where tcu.user_id in (select to_char(uu.userid) from uum_user@uum_link uu where uu.duty_level_code in (1380,1381) and uu.userid in (:userIds)) ";
			this.executeUpdate(QueryStringType.SQL, sql, params);
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("将局级人员信息更新回统一用户平台数据出错！");
		}
	}

	/**
	 * @author RongSL 2017年9月27日
	 */
	@Override
	public void updateJJUserByUnified(User us) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			params.put("userId", us.getUserId());
			params.put("phone", us.getMobileTelephone());
			params.put("folk", us.getFolkCode().getDataCode());
			params.put("position", us.getDutyRemark());
			String sql = " update " + SystemConstants.SYSTEM_CODE + "uum_user set mobile_telephone=:phone,folk_Code=:folk,duty_Remark=:position where userid=:userId";
			this.executeUpdate(QueryStringType.SQL, sql, params);
			sql = " update " + SystemConstants.SYSTEM_CODE + "uum_temp_user set mobile_telephone=:phone,folk_Code=:folk,duty_Remark=:position where userid=:userId";
			this.executeUpdate(QueryStringType.SQL, sql, params);
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("将局级人员信息更新回统一用户平台数据出错！");
		}
	}

	@Override
	public List<QueryClassUserBean> isFiveClass(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		StringBuffer sb = new StringBuffer();
		sb.append(" select distinct t.user_name as \"userName\",c.class_name as \"className\"  from training_class c, training_class_user t where c.class_id=t.class_id and c.class_id in (:classIds) and t.user_id in (:userIds) ");
		params.put("classIds", queryCommond.getSearchClassIds());
		params.put("userIds", queryCommond.getSearchUserIds());
		List<QueryClassUserBean> findList;
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(QueryClassUserBean.class);
		defaultQueryConditionBuilder.setBaseQueryString(sb.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		findList = (List<QueryClassUserBean>) this.findList(queryCondition);
		// String userNames = "";
		// if(findList.size()>0 && findList != null){
		// for(QueryClassUserBean bean : findList){
		// userNames = userNames + bean.getUserName() + "，";
		// }
		// }
		return findList;
	}

	/**
	 * 查询报名人数
	 * 
	 * @author RongSL 2017年10月25日
	 */
	@Override
	public Object findListUserCount(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> values = new HashMap<String, Object>();
		String sql = "select count(1) from training_class_user tcu where tcu.class_id=:classId and tcu.active_state=:activeState";
		values.put("classId", queryCommond.getSearchClassID());
		values.put("activeState", ClassUser.IS_ACTIVE_STATE_Y);
		List<Object> list = (List<Object>) this.executeFind(QueryStringType.SQL, sql, values);
		return list.get(0);
	}

	@Override
	public List<BaseData> findClassTypeList() throws Exception {
		String sql = "select bb.data_name as \"dataName\",bb.data_code as \"dataCode\" from base_basedata bb where bb.type_id='8a10e7f559394708015939d2e5340007' and bb.active_state=1 order by bb.order_num";
		List<BaseData> list = (List<BaseData>) this.executeFind(QueryStringType.SQL, sql, null, BaseData.class);
		return list;
	}

	@Override
	public List<UserClassTypeHour> findYearHourListByType(ClassUserQueryCommond queryCommond, String dataCode) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer("select ifnull(substr(t.classname_year, 1, 4),'') as \"hourYear\",ifnull(sum(u.training_hours),0) as \"hours\",u.USER_ID \"userId\" ");
		sql.append(" from " + SystemConstants.SYSTEM_CODE + "training_class_pxarchives t, " + SystemConstants.SYSTEM_CODE + "training_class_user_pxarchives u ");
		sql.append(" where t.class_id = u.class_id ");
		sql.append(" and t.class_type = :dataCode ");
//		sql.append(" and t.lock_state = " + ClassUser.IS_LOCK_STATE);
//		sql.append(" and u.auditing_state = " + ClassUser.IS_LOCK_STATE);
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchBeginDate())) {
			sql.append(" and t.begin_date>= date_FORMAT( :startDate , '%Y-%m-%d %H:%i:%s')");
			params.put("startDate", queryCommond.getSearchBeginDate());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchEndDate())) {
			sql.append(" and t.end_date <= date_FORMAT( :endDate , '%Y-%m-%d %H:%i:%s')");
			params.put("endDate", queryCommond.getSearchEndDate());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchUserID())){
			sql.append(" and u.user_id = :userId ");
			params.put("userId", queryCommond.getSearchUserID());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchUserIds())){
			sql.append(" and u.user_id in (:userIds) ");
			params.put("userIds", queryCommond.getSearchUserIds());
		}
		sql.append(" group by t.classname_year,u.USER_ID ");
		params.put("dataCode", dataCode);
		List<UserClassTypeHour> list = (List<UserClassTypeHour>) this.executeFind(QueryStringType.SQL, sql.toString(), params, UserClassTypeHour.class);
		return list;
	}
	
	@Override
	public List<QueryClassUserBean> findYearHourListByYear(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer("select "
				+ "ifnull(sum(u.training_hours),0) as \"hours\",u.USER_ID \"userId\","
				+ "ifnull(substr(t.classname_year, 1, 4), '' ) AS \"hourYear\","
				+ "ifnull(sum(case t.CLASS_TYPE when 'zhongydx' then u.TRAINING_HOURS else 0 end),0) \"zhongydxHour\", "
				+ "ifnull(sum(case t.CLASS_TYPE when 'ztb' then u.TRAINING_HOURS else 0 end),0) \"ztbHour\", "
				+ "ifnull(sum(case t.CLASS_TYPE when 'jwpx' then u.TRAINING_HOURS else 0 end),0) \"jwpxHour\","
				+ "ifnull(sum(case t.CLASS_TYPE when 'qit' then u.TRAINING_HOURS else 0 end),0) \"qitHour\","
				+ "ifnull(sum(case t.CLASS_TYPE when 'zhutb' then u.TRAINING_HOURS else 0 end),0) \"zhutbHour\" ");
		sql.append(" from " + SystemConstants.SYSTEM_CODE + "training_class_pxarchives t, " + SystemConstants.SYSTEM_CODE + "training_class_user_pxarchives u ");
		sql.append(" where t.class_id = u.class_id ");
//		sql.append(" and t.lock_state = " + ClassUser.IS_LOCK_STATE);
		sql.append(" and u.auditing_state = " + ClassUser.IS_LOCK_STATE);
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchBeginDate())) {
			sql.append(" and t.begin_date>= date_FORMAT( :startDate , '%Y-%m-%d %H:%i:%s')");
			params.put("startDate", queryCommond.getSearchBeginDate());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchEndDate())) {
			sql.append(" and t.end_date <= date_FORMAT( :endDate , '%Y-%m-%d %H:%i:%s')");
			params.put("endDate", queryCommond.getSearchEndDate());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchUserID())){
			sql.append(" and u.user_id = :userId ");
			params.put("userId", queryCommond.getSearchUserID());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchUserIds())){
			sql.append(" and u.user_id in (:userIds) ");
			params.put("userIds", queryCommond.getSearchUserIds());
		}
//		if(PropertyUtil.objectNotEmpty(year)){
//			sql.append(" and t.CLASSNAME_YEAR=:year ");
//			params.put("year", year+"年");
//		}
		sql.append(" group by t.classname_year,u.user_id ");
		List<QueryClassUserBean> list = (List<QueryClassUserBean>) this.executeFind(QueryStringType.SQL, sql.toString(), params, QueryClassUserBean.class);
		return list;
	}

	@Override
	public ClassUserValidCommond findUserInfoById(String searchUserID) throws Exception {
		ClassUserValidCommond commond = new ClassUserValidCommond();
		Map<String, Object> params = new HashMap<String, Object>();
		String sql = "select concat(u.userid,'') \"userId\",u.user_name \"userNameInfo\",u.login_id \"loginIdInfo\",d.depname \"depnameInfo\"," + "b.data_name \"rankInfo\",concat(u.duty_level_code,'') \"dutyLevelCodeInfo\" from " + SystemConstants.SYSTEM_CODE + "uum_user u ," + ""
				+ SystemConstants.SYSTEM_CODE + "uum_department d," + SystemConstants.SYSTEM_CODE + "uum_department_user t," + SystemConstants.SYSTEM_CODE + "uum_basedata b " + "where u.userid=t.userid and d.depid=t.depid and u.duty_level_code=b.data_id " + "and u.userid= :userId";
		params.put("userId", searchUserID);
		List<ClassUserValidCommond> list = (List<ClassUserValidCommond>) this.executeFind(QueryStringType.SQL, sql, params, ClassUserValidCommond.class);
		if (list != null && list.size() > 0) {
			return list.get(0);
		} else {
			commond = null;
		}
		return commond;
	}

	@SuppressWarnings("rawtypes")
	@Override
	public List findUserInfoByPaperNumOrUserName(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer(" select u.userid from  "+SystemConstants.SYSTEM_CODE+"uum_user u  where  ");
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchPaperNum())){
			sql.append("u.paper_num=:paperNum");
			params.put("paperNum", queryCommond.getSearchPaperNum());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchUserNameNoWhitespace())){
			sql.append(" replace(replace(u.user_name,' ',''),'　','') =:userName ");
//			params.put("userName", SqlQueryUtil.changeString(queryCommond.getSearchUserName().replace(" ", "")));
			params.put("userName", queryCommond.getSearchUserNameNoWhitespace().replaceAll("\\s*", ""));
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchSexCode())){
			sql.append(" and u.sex_code=:sexCode");
			params.put("sexCode", queryCommond.getSearchSexCode());
		}
		sql.append(" order by u.user_name ");
		List<?> list = (List<?>) this.executeFind(QueryStringType.SQL, sql.toString(), params);
		if (list != null && list.size() > 0) {
			return list;
		} else {
			return null;
		}
	}

	@Override
	public Double findCreditHour(String searchUserID, Integer year) throws Exception {
		Double count = new Double(0);
		StringBuffer sql = new StringBuffer();
		sql.append(" select format(ifnull(sum(ch.cur_hour),0),2) ");
		sql.append(" from " + SystemConstants.SYSTEM_CODE + "cre_cur_user_hour ch ");
		sql.append(" where ch.user_id = " + searchUserID);
		sql.append(" and ch.year = " + year);
		List<?> list = this.executeFind(QueryStringType.SQL, sql.toString(), null);
		if (list != null && list.size() > 0) {
			count = Double.valueOf(list.get(0).toString());
		}
		return count;
	}

	@Override
	public Double findZxAndGrantHour(String searchUserID, Integer year) throws Exception {
		Double count = new Double(0);
		StringBuffer sql = new StringBuffer();
		sql.append(" select format(a.hour + b.hour,2) " + " from (select ifnull(sum(ci.study_score), 0) hour" + 
		" from " + 
		SystemConstants.SYSTEM_CODE +  "course_info ci, " + 
		SystemConstants.SYSTEM_CODE + "USER_COURSE_LINK ucl" + 
		" where ucl.COURSE_ID = ci.id" + 
		" and ucl.USER_ID = " + searchUserID + 
		" and ucl.IS_PASSED = 1" + 
		" and ucl.year = " + year + ") a," + " (select ifnull(sum(t.hour_size), 0) hour" + " from " + SystemConstants.SYSTEM_CODE + "grant_hour t, " + SystemConstants.SYSTEM_CODE + "uum_user u" + " where t.login_id = u.login_id" + " and u.userid = "
				+ searchUserID + " and t.grant_year = " + year + ") b");
		List<?> list = this.executeFind(QueryStringType.SQL, sql.toString(), null);
		if (list != null && list.size() > 0) {
			count = Double.valueOf(list.get(0).toString());
		}
		return count;
	}

	@Override
	public Double findNtschoolHour(String searchUserID, Integer year) throws Exception {
		Double count = new Double(0);
		StringBuffer sql = new StringBuffer();
		sql.append(" select format(ifnull(sum(vcuc.get_class_score),0),2) " + " from " + SystemConstants.SYSTEM_CODE + "VNA_THEMATIC_CLASS vtc, " + " " + SystemConstants.SYSTEM_CODE + "vna_thematic_class_student vtcs, " + " " + SystemConstants.SYSTEM_CODE + "vna_class_user_certificate vcuc "
				+ " where vtc.thematic_class_id = vtcs.thematic_class_id " + " and vtc.thematic_class_id = vcuc.thematic_class_id " + " and vtcs.thematic_class_student_id = vcuc.thematic_class_student_id " + " and vtc.publish_state = 1 " + " and vtc.active_state = 1 "
				+ " and vcuc.is_get_class_score = 1 " + " and date_format(vcuc.certificate_date, '%Y') = " + year + " and vtcs.student_id = " + searchUserID);
		List<?> list = this.executeFind(QueryStringType.SQL, sql.toString(), null);
		if (list != null && list.size() > 0) {
			count = Double.valueOf(list.get(0).toString());
		}
		return count;
	}

	@Override
	public List<UserClassTypeHour> findHisYearUserHour(String searchUserID) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		String sql = " select cast(format(ifnull(t.course_score,0),2) as decimal) as \"zxHour\",date_FORMAT(t.data_date,'%Y') \"hourYear\"" + 
		" from " + 
				SystemConstants.SYSTEM_CODE + 
					"elms_study_hour t where t.user_id = :userId ";
		params.put("userId", searchUserID);
		List<UserClassTypeHour> list = (List<UserClassTypeHour>) this.executeFind(QueryStringType.SQL, sql, params, UserClassTypeHour.class);
		return list;
	}

		/**
		 * 我的参训情况
		 * 
		 * @author RongSL 2017年12月5日
		 */
		@Override
		public List<QueryClassUserBean> myClassTrainingList(ClassUserQueryCommond queryCommond) throws Exception {
			Map<String, Object> params = new HashMap<String, Object>();
			StringBuffer sb = new StringBuffer();
			sb.append(" select distinct tc.class_num \"classNum\",tc.class_id \"classId\",tc.class_name \"className\"," 
			+ "tcu.class_user_id \"classUserId\",bb.data_name \"classType\",tc.begin_date \"beginDate\","
			+ "tc.end_date \"endDate\",tc.enter_begin_time \"enterBeginTime\",bu.login_id as \"loginID\", " 
			+ "tcu.auditing_state \"auditingState\",tcu.study_id \"attachmentID\",tc.sponsor_name \"sponsorName\"," 
			+ "qso.questionnaire_id as \"questionnaireId\" " 
			+ "from training_class_user tcu  "
			+ "left join training_class tc on tcu.class_id=tc.class_id " 
			+ "left join base_user bu on bu.userid=tcu.user_id " 
			+ "left join questionnaire_survey_object qso on qso.survey_object_id=tc.class_id " 
			+ "left join base_basedata bb on tc.class_type=bb.data_code "
			+ "where tc.publish_state=1 and tcu.auditing_state in (1,5) and tcu.user_id=:userId  ");
			params.put("userId", queryCommond.getSearchUserID());
			if (queryCommond.getSortInfos() != null) {
				SortInfoBean sortInfoBean = queryCommond.getSortInfos();
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
					sb.append(" order by " + sortInfoBean.getProperty().trim());
					if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
						sb.append(" " + sortInfoBean.getDirection().trim());
					}
				}
			} else {
				sb.append("  order by tc.open_state desc ");
			}
	
			List<QueryClassUserBean> findList;
			IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
			defaultQueryConditionBuilder.setResultObject(QueryClassUserBean.class);
			defaultQueryConditionBuilder.setBaseQueryString(sb.toString());
			defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
			IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
			queryCondition.setParameters(params);
			// 查询总数量
			Long count = this.count(queryCondition);
			if (queryCommond.getPage() == -1) {
				findList = (List<QueryClassUserBean>) this.findList(queryCondition);
			} else {
				int firstResult = queryCommond.calculate(count.longValue());
				findList = (List<QueryClassUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
			}
			return findList;
		}

	/**
	 * 学员统计（管理员用）
	 * @author RongSL 2017年12月19日
	 */
	public List<QueryClassUserBean> findUserStatisticsByAdmin(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		StringBuffer sb = new StringBuffer();
		sb.append(" select bu.userid \"userId\",bu.LOGIN_ID \"loginID\",bu.USER_NAME \"userName\",bd.depname \"depName\",bu.duty_Level_Code \"dutyLevelCode\" " + "from  " + 
		SystemConstants.SYSTEM_CODE + "uum_user bu  " + "left join  " + 
		SystemConstants.SYSTEM_CODE + "uum_department_user bdu on bdu.userid=bu.userid  left join  " + 
		SystemConstants.SYSTEM_CODE + "uum_department bd on bdu.depid=bd.depid  left join  " + 
		SystemConstants.SYSTEM_CODE + "UUM_SYS_DEP_SYSROLE_GRP_VIEW usds on bu.USERID=usds.USERID " + 
		"where bd.treepath like :treePath and bu.ACTIVE_STATE=1 " + 
		"and bdu.active_state=1 and bd.active_state=1 and usds.userid is null  "
			// + "and bu.userid='1815180' "
			+ "order by bu.LOGIN_ID");
		params.put("treePath", queryCommond.getSearchTreePath() + "%");
		// if (queryCommond.getSortInfos() != null) {
		// SortInfoBean sortInfoBean = queryCommond.getSortInfos();
		// if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
		// sb.append( " order by " + sortInfoBean.getProperty().trim());
		// if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
		// sb.append( " "+sortInfoBean.getDirection().trim());
		// }
		// }
		// } else {
		// sb.append(" order by bu.LOGIN_ID ");
		// }

		List<QueryClassUserBean> findList;
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(QueryClassUserBean.class);
		defaultQueryConditionBuilder.setBaseQueryString(sb.toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		// 查询总数量
		Long count = this.count(queryCondition);
		if (queryCommond.getLimit() == -1) {
			findList = (List<QueryClassUserBean>) this.findList(queryCondition);
		} else {
			int firstResult = queryCommond.calculate(count.longValue());
			findList = (List<QueryClassUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
		}
		return findList;
	}

	/**
	 * 查询单个学员统计数据
	 * 
	 * @author RongSL 2017年12月20日
	 */
	public UserClassTypeHour findUserClassTypeHour(ClassUserQueryCommond queryCommond) throws Exception {
		String hql = " from  UserClassTypeHour u where u.userId=" + queryCommond.getSearchUserID() + " and u.hourYear=" + queryCommond.getYear();
		List<?> list = this.executeFind(QueryStringType.HQL, hql, null);
		if (list.isEmpty()) {
			return null;
		} else
			return (UserClassTypeHour) list.get(0);
	}

	/**
	 * 查询学员统计数据列表
	 * 
	 * @author RongSL 2017年12月20日
	 */
	public List<QueryClassUserBean> findUserClassTypeHourList(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String, Object> params = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer("select count(*) ");
		StringBuffer selectSql = new StringBuffer(" SELECT" + 
				"	ROUND(IFNULL(b.trainingHour,0),0)+a.zxZsHour \"hours\"," + 
				"	ROUND(IFNULL(c.trainingHour,0),0) \"zhongydxHour\"," + 
				"	ROUND(IFNULL(d.trainingHour,0),0) \"qitHour\"," + 
				"	ROUND(IFNULL(e.trainingHour,0),0) \"zhutbHour\"," + 
				"	ROUND(IFNULL(f.trainingHour,0),0) \"jwpxHour\"," + 
				"	ROUND(IFNULL(g.trainingHour,0),0) \"ztbHour\"," + 
				"	a.hourYear \"hourYear\"," + 
				"	a.isStandard \"isStandard\"," + 
				"	a.zxHour \"zxHour\"," + 
				"	a.zxZsHour \"zxZsHour\"," + 
				"	a.userId \"userId\"," + 
				"	a.loginID \"loginID\"," + 
				"	a.sexName \"sexName\"," + 
				"	a.userName \"userName\"," + 
				"	a.depName \"depName\"," + 
				"	a.dutyLevelCode \"dutyLevelCode\"," + 
				"	a.officeholdingDateStr \"officeholdingDateStr\"," + 
				"	a.dutyLevelName \"dutyLevelName\"," + 
				"	a.mobileTelephone \"mobileTelephone\"" + 
				"FROM" + 
				"	(" + 
				"		SELECT" + 
				"			uct.hour_year \"hourYear\"," + 
				"			CASE" + 
				"		WHEN uct.hours >= 110 THEN" + 
				"			1" + 
				"		ELSE" + 
				"			2" + 
				"		END \"isStandard\"," + 
				"		uct.zx_Hour \"zxHour\"," + 
				"		uct.zxZs_Hour \"zxZsHour\"," + 
				"		bu.userid \"userId\"," + 
				"		bu.LOGIN_ID \"loginID\"," + 
				"		ubb.data_name \"sexName\"," + 
				"		bu.USER_NAME \"userName\"," + 
				"		bd.depname \"depName\"," + 
				"		bu.duty_Level_Code \"dutyLevelCode\"," + 
				"		date_format(" + 
				"			bu.officeholding_date," + 
				"			'%Y-%m-%d'" + 
				"		) \"officeholdingDateStr\"," + 
				"		ub.data_name \"dutyLevelName\"," + 
				"		bu.MOBILE_TELEPHONE \"mobileTelephone\"" + 
				"	FROM" + 
				"		user_class_type_hour uct" + 
				"	LEFT JOIN uum_xch.uum_user bu ON uct.user_id = bu.USERID" + 
				"	LEFT JOIN uum_xch.uum_basedata ub ON ub.data_id = bu.DUTY_LEVEL_CODE" + 
				"	LEFT JOIN uum_xch.uum_basedata ubb ON ubb.data_id = bu.sex_code" + 
				"	LEFT JOIN uum_xch.uum_department_user bdu ON bdu.userid = bu.userid" + 
				"	LEFT JOIN uum_xch.uum_department bd ON bdu.depid = bd.depid" + 
				"	LEFT JOIN uum_xch.uum_sys_dep_sysrole_grp_view usds ON bu.USERID = usds.USERID WHERE 1=1 ");
		/*sql.append("uct.hour_year \"hourYear\",uct.hours \"hours\",case when uct.hours>=110 then 1 else 2 end \"isStandard\"," 
				+ "uct.zx_Hour \"zxHour\", uct.jwpx_Hour \"jwpxHour\",uct.qit_Hour \"qitHour\",uct.zhongydx_Hour \"zhongydxHour\", "
				+ "uct.zhutb_Hour \"zhutbHour\",uct.ztb_Hour \"ztbHour\",uct.zxZs_Hour \"zxZsHour\" ");
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchTreePath())) {
			sql.append(",bu.userid \"userId\",bu.LOGIN_ID \"loginID\",ubb.data_name \"sexName\",bu.USER_NAME \"userName\",bd.depname \"depName\"," 
		+ "bu.duty_Level_Code \"dutyLevelCode\",date_format(bu.officeholding_date,'%Y-%m-%d')  \"officeholdingDateStr\""
		+ ",ub.data_name \"dutyLevelName\",bu.MOBILE_TELEPHONE \"mobileTelephone\" ");
		}*/
		sql.append("from user_class_type_hour uct ");
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchTreePath())) {
			sql.append("left join " + SystemConstants.SYSTEM_CODE + "uum_user bu on uct.user_id=bu.USERID " + 
		"left join " + SystemConstants.SYSTEM_CODE + "uum_basedata ub on ub.data_id=bu.DUTY_LEVEL_CODE " + 
					"left join " + SystemConstants.SYSTEM_CODE + "uum_basedata ubb on ubb.data_id=bu.sex_code " + 
		"left join " + SystemConstants.SYSTEM_CODE + "uum_department_user bdu ON bdu.userid = bu.userid " + 
					"left join " + SystemConstants.SYSTEM_CODE + "uum_department bd ON bdu.depid = bd.depid " + 
		"left join " + SystemConstants.SYSTEM_CODE + "uum_sys_dep_sysrole_grp_view usds ON bu.USERID = usds.USERID ");
//					"left join " + SystemConstants.SYSTEM_CODE + "training_class_user_pxarchives tcup on uct.user_id=tcup.USER_ID 
		}
		StringBuffer conditionSql = new StringBuffer();
		sql.append("WHERE 1=1 ");
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchTreePath())) {
			conditionSql.append("and bd.treepath LIKE :treePath AND bu.ACTIVE_STATE = 1 AND bdu.active_state = 1 AND bd.active_state = 1 AND usds.userid is null ");
			params.put("treePath", queryCommond.getSearchTreePath() + "%");
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserID())) {
			conditionSql.append("and uct.user_id=:userId ");
			params.put("userId", queryCommond.getSearchUserID());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchSexCode())) {
			conditionSql.append("and bu.sex_code=:sexCode ");
			params.put("sexCode", queryCommond.getSearchSexCode());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())) {
			conditionSql.append("and bu.user_name like :userName ");
			params.put("userName", "%" + queryCommond.getSearchUserName() + "%");
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchLoginID())) {
			conditionSql.append("and bu.login_id=:loginId ");
			params.put("loginId", queryCommond.getSearchLoginID());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchYear()) && queryCommond.getSearchYear().intValue() != -1) {
			conditionSql.append("and uct.hour_Year=:hourYear ");
			params.put("hourYear", queryCommond.getSearchYear());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getDutyLevelId())) {
			conditionSql.append("and bu.duty_Level_Code=:dutyLevelCode ");
			params.put("dutyLevelCode", queryCommond.getDutyLevelId());
		}
//		StringBuffer sb=new StringBuffer(" 1=1 ");
//		if(PropertyUtil.objectNotEmpty(queryCommond.getJudgeOne())){
//			if (PropertyUtil.objectNotEmpty(queryCommond.getIsStandard())) {
//				if (queryCommond.getIsStandard() == 1)
//					sb.append("and uct.hours<110 ");
//				else
//					sb.append("and uct.hours>=110 ");
//			}
//		}else{
			if (PropertyUtil.objectNotEmpty(queryCommond.getIsStandard())) {
				if (queryCommond.getIsStandard() == 1)
					conditionSql.append("and uct.hours>=110 ");
				else
					conditionSql.append("and uct.hours<110 ");
			}
//		}
//		if(PropertyUtil.objectNotEmpty(queryCommond.getJudgeTwo())){
//			if(queryCommond.getJudgeTwo().intValue()==1)
//				sb.append(" and ");
//			else
//				sb.append(" or  ");
//		}
		
//		if(PropertyUtil.objectNotEmpty(queryCommond.getJudgeThree())){
//			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDeptName())) {
//				if (!PropertyUtil.objectNotEmpty(queryCommond.getJudgeTwo())) {
//					sb.append(" and ");
//				}
//				sb.append("bd.depname not like :depName ");
//				params.put("depName", "%" + queryCommond.getSearchDeptName() + "%");
//			}
//		}else{
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDeptName())) {
//				if (!PropertyUtil.objectNotEmpty(queryCommond.getJudgeTwo())) {
//					sb.append(" and ");
//				}
				conditionSql.append(" and bd.depname like :depName ");
				params.put("depName", "%" + queryCommond.getSearchDeptName() + "%");
			}
//		}
//		if(PropertyUtil.objectNotEmpty(queryCommond.getJudgeFour())){
//			if(queryCommond.getJudgeFour().intValue()==1)
//				sb.append(" and ");
//			else
//				sb.append(" or  ");
//		}
//		if(PropertyUtil.objectNotEmpty(queryCommond.getJudgeFive())){
//			if(PropertyUtil.objectNotEmpty(queryCommond.getSearchYearStart())&&PropertyUtil.objectNotEmpty(queryCommond.getSearchYearEnd())){
//				sb.append("(");
//			}
//			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchYearStart())) {
//				sb.append("uct.hour_Year <:startYear ");
//				params.put("startYear", queryCommond.getSearchYearStart());
//			}
//			if(PropertyUtil.objectNotEmpty(queryCommond.getSearchYearStart())&&PropertyUtil.objectNotEmpty(queryCommond.getSearchYearEnd())){
//				sb.append(" and ");
//			}
//			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchYearEnd())) {
//				sb.append("uct.hour_Year >:endYear ");
//				params.put("endYear", queryCommond.getSearchYearEnd());
//			}
//			if(PropertyUtil.objectNotEmpty(queryCommond.getSearchYearStart())&&PropertyUtil.objectNotEmpty(queryCommond.getSearchYearEnd())){
//				sb.append(")");
//			}
//		}else{
//			if(PropertyUtil.objectNotEmpty(queryCommond.getSearchYearStart())&&PropertyUtil.objectNotEmpty(queryCommond.getSearchYearEnd())){
//				sb.append("(");
//			}
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchYearStart())) {
				conditionSql.append(" and uct.hour_Year >=:startYear ");
				params.put("startYear", queryCommond.getSearchYearStart());
			}
//			if(PropertyUtil.objectNotEmpty(queryCommond.getSearchYearStart())&&PropertyUtil.objectNotEmpty(queryCommond.getSearchYearEnd())){
//				sb.append(" and ");
//			}
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchYearEnd())) {
				conditionSql.append(" and uct.hour_Year <=:endYear ");
				params.put("endYear", queryCommond.getSearchYearEnd());
			}
//			if(PropertyUtil.objectNotEmpty(queryCommond.getSearchYearStart())&&PropertyUtil.objectNotEmpty(queryCommond.getSearchYearEnd())){
//				sb.append(")");
//			}
//		}
//		if(!" 1=1 ".equals(sb.toString())){
//			sql.append(" and ("+sb+")");
//		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getIsTrainingClass())) {
			if (queryCommond.getIsTrainingClass() == 1)
				conditionSql.append(" and uct.is_trainingclass<>0 ");
			else
				conditionSql.append(" and uct.is_trainingclass=0 ");
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchOfficeholdingDate())){
			conditionSql.append(" and date_format(bu.OFFICEHOLDING_DATE,'%Y-%m-%d')=:officeholdingDate ");
			params.put("officeholdingDate", queryCommond.getSearchOfficeholdingDate());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchOfficeholdingDateBegin())){
			conditionSql.append(" and bu.officeholding_date>=:TRDateMin ");
			params.put("TRDateMin",queryCommond.getSearchOfficeholdingDateBegin());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchOfficeholdingDateEnd())){
			conditionSql.append(" and bu.officeholding_date<=:TRDateMax ");
			params.put("TRDateMax",queryCommond.getSearchOfficeholdingDateEnd());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getIsPromoteStandard())) {
			// 1.对提任时间满一年，但是未达标的学员（学时未达到110学时），通过提醒的方式通知管理员
			// 例如：2017-10-1日任职，则2018-9-30时进行是否提醒判断。
			conditionSql.append(" and date_add(date_add(date_format(bu.OFFICEHOLDING_DATE,'%Y-%m-%d'),INTERVAL 1 YEAR),INTERVAL -1 day)=date_format(now(),'%Y-%m-%d') ");
		}
		//默认查询5年内的所有培训班
		conditionSql.append(" and uct.hour_Year>=YEAR(now())-5 ");
		if (queryCommond.getSortInfos() != null) {
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
				conditionSql.append(" order by " + sortInfoBean.getProperty().trim());
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
					conditionSql.append(" " + sortInfoBean.getDirection().trim());
				}
			}
		} else {
			if (PropertyUtil.objectNotEmpty(queryCommond.getSearchTreePath())) {
				conditionSql.append(" ORDER BY uct.hour_year desc,bu.duty_level_code asc,bu.login_id asc ");
			} else
				conditionSql.append("  order by uct.hour_year desc");
		}
		StringBuffer joinSql = new StringBuffer(" ) a" + 
				" LEFT JOIN (" + 
				"	SELECT" + 
				"		SUM(c.training_hour) AS trainingHour," + 
				"		cu.user_id,c.CLASSNAME_YEAR" + 
				"	FROM" + 
				"		uum_xch.training_class_user_pxarchives cu" + 
				"	LEFT JOIN uum_xch.training_class_pxarchives c ON cu.class_id = c.class_id" + 
				"	WHERE" + 
				"		c.lock_state = 1" );
				if (PropertyUtil.objectNotEmpty(queryCommond.getSearchYear()) && queryCommond.getSearchYear().intValue() != -1) {
					joinSql.append("	AND c.classname_year = :hourYear"  );
				}
				joinSql.append("	GROUP BY" + 
				"		cu.user_id,c.CLASSNAME_YEAR" + 
				") b ON a.userId = b.user_id and b.CLASSNAME_YEAR like CONCAT('%',a.hourYear,'%')  " + 
				"LEFT JOIN (" + 
				"	SELECT" + 
				"		SUM(c.training_hour) AS trainingHour," + 
				"		cu.user_id,c.CLASSNAME_YEAR" + 
				"	FROM" + 
				"		uum_xch.training_class_user_pxarchives cu" + 
				"	LEFT JOIN uum_xch.training_class_pxarchives c ON cu.class_id = c.class_id" + 
				"	WHERE" + 
				"		c.lock_state = 1" + 
				"	AND c.class_type = 'zhongydx'" );
				if (PropertyUtil.objectNotEmpty(queryCommond.getSearchYear()) && queryCommond.getSearchYear().intValue() != -1) {
					joinSql.append("	AND c.classname_year = :hourYear"  );
				}
				joinSql.append("	GROUP BY" + 
				"		cu.user_id,c.CLASSNAME_YEAR" + 
				") c ON a.userId = c.user_id and c.CLASSNAME_YEAR like CONCAT('%',a.hourYear,'%')  " + 
				"LEFT JOIN (" + 
				"	SELECT" + 
				"		SUM(c.training_hour) AS trainingHour," + 
				"		cu.user_id,c.CLASSNAME_YEAR" + 
				"	FROM" + 
				"		uum_xch.training_class_user_pxarchives cu" + 
				"	LEFT JOIN uum_xch.training_class_pxarchives c ON cu.class_id = c.class_id" + 
				"	WHERE" + 
				"		c.lock_state = 1" + 
				"	AND c.class_type = 'qit'" );
				if (PropertyUtil.objectNotEmpty(queryCommond.getSearchYear()) && queryCommond.getSearchYear().intValue() != -1) {
					joinSql.append("	AND c.classname_year = :hourYear"  );
				}
				joinSql.append("	GROUP BY" + 
				"		cu.user_id,c.CLASSNAME_YEAR" + 
				") d ON a.userId = d.user_id and d.CLASSNAME_YEAR like CONCAT('%',a.hourYear,'%')  " + 
				"LEFT JOIN (" + 
				"	SELECT" + 
				"		SUM(c.training_hour) AS trainingHour," + 
				"		cu.user_id,c.CLASSNAME_YEAR" + 
				"	FROM" + 
				"		uum_xch.training_class_user_pxarchives cu" + 
				"	LEFT JOIN uum_xch.training_class_pxarchives c ON cu.class_id = c.class_id" + 
				"	WHERE" + 
				"		c.lock_state = 1" + 
				"	AND c.class_type = 'zhutb'" );
				if (PropertyUtil.objectNotEmpty(queryCommond.getSearchYear()) && queryCommond.getSearchYear().intValue() != -1) {
					joinSql.append("	AND c.classname_year = :hourYear"  );
				}
				joinSql.append("	GROUP BY" + 
				"		cu.user_id,c.CLASSNAME_YEAR" + 
				") e ON a.userId = e.user_id and e.CLASSNAME_YEAR like CONCAT('%',a.hourYear,'%')  " + 
				"LEFT JOIN (" + 
				"	SELECT" + 
				"		SUM(c.training_hour) AS trainingHour," + 
				"		cu.user_id,c.CLASSNAME_YEAR" + 
				"	FROM" + 
				"		uum_xch.training_class_user_pxarchives cu" + 
				"	LEFT JOIN uum_xch.training_class_pxarchives c ON cu.class_id = c.class_id" + 
				"	WHERE" + 
				"		c.lock_state = 1" + 
				"	AND c.class_type = 'jwpx'" );
				if (PropertyUtil.objectNotEmpty(queryCommond.getSearchYear()) && queryCommond.getSearchYear().intValue() != -1) {
					joinSql.append("	AND c.classname_year = :hourYear"  );
				}
				joinSql.append("	GROUP BY" + 
				"		cu.user_id,c.CLASSNAME_YEAR" + 
				") f ON a.userId = f.user_id and f.CLASSNAME_YEAR like CONCAT('%',a.hourYear,'%')  " + 
				"LEFT JOIN (" + 
				"	SELECT" + 
				"		SUM(c.training_hour) AS trainingHour," + 
				"		cu.user_id ,c.CLASSNAME_YEAR " + 
				"	FROM" + 
				"		uum_xch.training_class_user_pxarchives cu" + 
				"	LEFT JOIN uum_xch.training_class_pxarchives c ON cu.class_id = c.class_id" + 
				"	WHERE" + 
				"		c.lock_state = 1" + 
				"	AND c.class_type = 'ztb'" );
				if (PropertyUtil.objectNotEmpty(queryCommond.getSearchYear()) && queryCommond.getSearchYear().intValue() != -1) {
					joinSql.append("	AND c.classname_year = :hourYear"  );
				}
				joinSql.append("	GROUP BY" + 
				"		cu.user_id ,c.CLASSNAME_YEAR" + 
				") g ON a.userId = g.user_id and g.CLASSNAME_YEAR like CONCAT('%',a.hourYear,'%')  ");
		List<QueryClassUserBean> findList;
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(QueryClassUserBean.class);
		defaultQueryConditionBuilder.setBaseQueryString(selectSql.append(conditionSql).append(joinSql).toString());
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		// 查询总数量
		List<BigInteger> countList = (List<BigInteger>) this.executeFind(QueryStringType.SQL, sql.append(conditionSql).toString(), params);
		Long count = (long) countList.get(0).intValue();
		
		if (queryCommond.getLimit() == -1) {
			findList = (List<QueryClassUserBean>) this.findList(queryCondition);
		} else {
			int firstResult = queryCommond.calculate(count.longValue());
			findList = (List<QueryClassUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
		}
		return findList;
	}
	
	@Override
	public List<QueryClassUserBean> findStatisticsUserByClass(ClassUserQueryCommond queryCommond) throws Exception {
		Map<String,Object> params=new HashMap<String,Object>();
		StringBuffer sb=new StringBuffer("select count(tc.CLASS_ID) \"classCount\","
				+ "bu.userid \"userId\", bu.login_id \"loginID\", bu.user_name \"userName\","
				+ "ub.data_name \"dutyLevelName\", ubbb.data_name \"sexName\","
				+ "date_format(bu.officeholding_date,'%Y-%m-%d') \"officeholdingDateStr\","
				+ "bd.depname \"depName\",bu.mobile_telephone \"mobileTelephone\" "
				+ "from  "+SystemConstants.SYSTEM_CODE+"training_class_user_pxarchives tcu  "
				+ "left join  "+SystemConstants.SYSTEM_CODE+"uum_user bu on tcu.USER_ID=bu.USERID "
				+ "left join  "+SystemConstants.SYSTEM_CODE+"training_class_pxarchives tc on tcu.CLASS_ID=tc.CLASS_ID "
				+ "left join  "+SystemConstants.SYSTEM_CODE+"uum_basedata ub on bu.DUTY_LEVEL_CODE=ub.data_id "
				+ "left join  "+SystemConstants.SYSTEM_CODE+"uum_basedata ubbb ON bu.SEX_CODE = ubbb.data_id "
				+ "left join  "+SystemConstants.SYSTEM_CODE+"uum_department_user udu on udu.userid=bu.USERID "
				+ "left join  "+SystemConstants.SYSTEM_CODE+"uum_department bd on udu.depid=bd.depid "
				+ "left join base_basedata ubb on ubb.data_code=tc.class_type "
				+ "where  bu.USERID is not null and tc.active_state = 1");
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchLoginID())){
			sb.append(" and bu.login_id=:loginId ");
			params.put("loginId", queryCommond.getSearchLoginID());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchNoUserIds())){
			sb.append(" and bu.userid not in (:userIds) ");
			params.put("userIds", queryCommond.getSearchNoUserIds());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassID())){
			sb.append(" and tc.class_id=:classId ");
			params.put("classId",queryCommond.getSearchClassID());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassTypeCode())){
			sb.append(" and tc.class_type=:classType ");
			params.put("classType",queryCommond.getSearchClassTypeCode());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchClassType())){
			sb.append(" and tc.class_type=:classType ");
			params.put("classType",queryCommond.getSearchClassType());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchUserName())){
			sb.append(" and bu.user_name like :userName ");
			params.put("userName","%"+queryCommond.getSearchUserName()+"%");
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchBeginDate())){
			sb.append(" and tc.begin_date>=:beginDate ");
			params.put("beginDate",queryCommond.getSearchBeginDate());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchEndDate())){
			sb.append(" and tc.end_date<=:endDate ");
			params.put("endDate",queryCommond.getSearchEndDate());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchDepId())){
			sb.append(" and bd.treepath like CONCAT((select treepath from "+SystemConstants.SYSTEM_CODE+"uum_department bd2 where bd2.depid=:depId),'%') ");
			params.put("depId",queryCommond.getSearchDepId());
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getDutyLevelId())) {
			sb.append("and bu.duty_Level_Code=:dutyLevelCode ");
			params.put("dutyLevelCode", queryCommond.getDutyLevelId());
		}
		//学员所在单位
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchDeptName())) {
			queryCommond.setSearchDeptName(queryCommond.getSearchDeptName().replace(",", "，"));
			if (queryCommond.getSearchDeptName().split("，").length > 1) {
				sb.append(" and (");
				for (String s : queryCommond.getSearchDeptName().split("，")) {
					sb.append(" bd.depname like '" + SqlQueryUtil.changeString(s) + "' or");
				}
				sb = new StringBuffer(sb.substring(0, sb.length() - 2));
				sb.append(")");
			} else {
				sb.append(" and bd.depname like :depName");
				params.put("depName", SqlQueryUtil.changeString(queryCommond.getSearchDeptName().replace(" ", "")));
			}
		}
		//主办单位
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchSponsorName())) {
			queryCommond.setSearchSponsorName(queryCommond.getSearchSponsorName().replace(",", "，"));
			if (queryCommond.getSearchSponsorName().split("，").length > 1) {
				sb.append(" and (");
				for (String s : queryCommond.getSearchSponsorName().split("，")) {
					sb.append(" tc.sponsor_name like '" + SqlQueryUtil.changeString(s) + "' or");
				}
				sb = new StringBuffer(sb.substring(0, sb.length() - 2));
				sb.append(")");
			} else {
				sb.append(" and tc.sponsor_name like :sponsorName");
				params.put("sponsorName", SqlQueryUtil.changeString(queryCommond.getSearchSponsorName().replace(" ", "")));
			}
		}
		if (PropertyUtil.objectNotEmpty(queryCommond.getSearchClassName())) {
			queryCommond.setSearchClassName(queryCommond.getSearchClassName().replace(",", "，"));
			if (queryCommond.getSearchClassName().split("，").length > 1) {
				sb.append(" and (");
				for (String s : queryCommond.getSearchClassName().split("，")) {
					sb.append(" tc.class_name like '" + SqlQueryUtil.changeString(s) + "' or");
				}
				sb = new StringBuffer(sb.substring(0, sb.length() - 2));
				sb.append(")");
			} else {
				sb.append(" and tc.class_name like :className");
				params.put("className", SqlQueryUtil.changeString(queryCommond.getSearchClassName().replace(" ", "")));
			}
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingHourMin())){
			sb.append(" and tc.training_hour>=:trainingHourMin ");
			params.put("trainingHourMin", queryCommond.getSearchTrainingHourMin());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchTrainingHourMax())){
			sb.append(" and tc.training_hour<=:trainingHourMax ");
			params.put("trainingHourMax", queryCommond.getSearchTrainingHourMax());
		}
		if(PropertyUtil.objectNotEmpty(queryCommond.getSearchLockState())||null!=queryCommond.getSearchLockState()){
			sb.append(" and tc.lock_state=:lockState ");
			params.put("lockState", queryCommond.getSearchLockState());
		}
		
		sb.append(" group by bu.userid,bu.login_id,bu.user_name,"
				+ "bu.officeholding_date,"
				+ "ub.data_name,bd.depname");
		// 增加排序
		if (queryCommond.getSortInfos() != null) {
			SortInfoBean sortInfoBean = queryCommond.getSortInfos();
			if (PropertyUtil.objectNotEmpty(sortInfoBean.getProperty())) {
				sb.append(" order by " + sortInfoBean.getProperty().trim());
				if (PropertyUtil.objectNotEmpty(sortInfoBean.getDirection())) {
					sb.append(" " + sortInfoBean.getDirection().trim());
				}
			}
		} else {
			sb.append(" order by bu.login_id");
		}
		IConditionBuilder defaultQueryConditionBuilder = new DefaultQueryConditionBuilder();
		defaultQueryConditionBuilder.setResultObject(QueryClassUserBean.class);
		defaultQueryConditionBuilder.setBaseQueryString("");
		defaultQueryConditionBuilder.setQueryStringType(QueryStringType.SQL);
		IPagingQueryCondition queryCondition = (IPagingQueryCondition) defaultQueryConditionBuilder.buliderQueryCondition();
		queryCondition.setParameters(params);
		// 查询总数量
		StringBuffer sb2=new StringBuffer("select * from ("+sb+") t");
		queryCondition.setQueryString(sb2.toString());
		Long count = this.count(queryCondition);
		queryCondition.setQueryString(sb.toString());
		List<QueryClassUserBean> findList;
		if (queryCommond.getLimit() == -1) {
			findList = (List<QueryClassUserBean>) this.findList(queryCondition);
		} else {
			int firstResult = queryCommond.calculate(count.longValue());
			queryCondition.setCount(count);
			findList = (List<QueryClassUserBean>) this.findList(queryCondition, firstResult, queryCommond.getLimit());
		}
		return findList;
	}
	
	/**
	 * 保存撤销报名的用户id
	 * @param annulUser
	 * @throws Exception
	 */
	public void saveAnnulUser(String userId) throws Exception{
		Map<String, Object> params = new HashMap<String, Object>();
		String sql = "insert into annul_user (ANNUL_USER_ID,USER_ID) values( :annulUserId,:userId)";
		params.put("annulUserId", UUID.randomUUID().toString().replace("-", ""));
		params.put("userId", userId);
		this.executeUpdate(QueryStringType.SQL, sql, params);
	}
}
